public DLNAMediaDatabase(String name) {
String dir = "database";
dbName = name;
File fileDir = new File(dir);
if (Platform.isWindows()) {
String profileDir = configuration.getProfileDirectory();
url = String.format("jdbc:h2:%s\\%s/%s", profileDir, dir, dbName);
fileDir = new File(profileDir, dir);
} else {
url = Constants.START_URL + dir + "/" + dbName;
}
dbDir = fileDir.getAbsolutePath();
LOGGER.debug("Using database URL: " + url);
LOGGER.info("Using database located at: " + dbDir);
try {
Class.forName("org.h2.Driver");
} catch (ClassNotFoundException e) {
LOGGER.error(null, e);
}
JdbcDataSource ds = new JdbcDataSource();
ds.setURL(url);
ds.setUser("sa");
ds.setPassword("");
cp = JdbcConnectionPool.create(ds);
}
private EntityManagerFactory makeEntityManagerFactory(String s) throws SQLException {
Properties props = new Properties();
String url = String.format("jdbc:h2:mem:%s;DB_CLOSE_DELAY=-1", s);
props.put("javax.persistence.jdbc.url", url);
props.put("javax.persistence.jdbc.driver", "org.h2.Driver");
props.put("javax.persistence.transactionType", "RESOURCE_LOCAL");
// OpenJPA support
props.put("openjpa.Connection2URL", url);
props.put("openjpa.Connection2DriverName", "org.h2.Driver");
props.put("openjpa.jdbc.SynchronizeMappings", "buildSchema(SchemaAction='add')");
props.put("openjpa.ConnectionRetainMode", "always");
props.put("openjpa.ConnectionFactoryMode", "local");
// eclipse-link support
props.put("eclipselink.ddl-generation", "create-tables");
props.put("eclipselink.ddl-generation.output-mode", "database");
// Hibernate support
props.put("hibernate.hbm2ddl.auto", "create-drop");
props.put("hibernate.connection.driver_class", "org.h2.Driver");
props.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
props.put("hibernate.connection.url", url);
props.put("hibernate.show_sql", "true");
JdbcDataSource dataSource = new JdbcDataSource();
dataSource.setURL(url);
String dataSourceUrl = "java:/dummyDataSource_" + s;
try {
new InitialContext().bind(dataSourceUrl, dataSource);
} catch (NamingException e) {
throw new AssertionError(e);
}
props.put("hibernate.connection.datasource", dataSourceUrl);
return Persistence.createEntityManagerFactory(s, props);
}
public void setUp() throws Exception {
JdbcDataSource ds = new JdbcDataSource();
ds.setURL("jdbc:h2:mem:test;MVCC=TRUE");
dbi = new DBI(ds);
handle = dbi.open();
handle.execute("create table something (id int primary key, name varchar(100))");
}
@Before
public void createInMemoryDatabase() throws DataAccessException, SQLException {
JdbcDataSource dataSource = new JdbcDataSource();
dataSource.setURL("jdbc:h2:mem:" + getClass().getSimpleName() + "_" + uniqueId + ";MVCC=TRUE");
uniqueId++;
CompositeDatasourceTransaction.registerDataSource("test", dataSource);
connectionToKeepInMemoryDatabaseAlive = dataSource.getConnection();
CurrentTransaction.create();
}
/** Returns a data source for the HSQL database. */
protected DataSource createDataSource() throws SQLException {
DataSource candidate = super.createDataSource();
if (candidate instanceof JdbcDataSource) {
return candidate;
}
final JdbcDataSource source = new JdbcDataSource();
File directory = getDirectory();
if (directory != null) {
/*
* Constructs the full path to the HSQL database. Note: we do not use
* File.toURI() because HSQL doesn't seem to expect an encoded URL
* (e.g. "%20" instead of spaces).
*/
final StringBuilder url = new StringBuilder(PREFIX);
final String path = directory.getAbsolutePath().replace(File.separatorChar, '/');
if (path.length() == 0 || path.charAt(0) != '/') {
url.append('/');
}
url.append(path);
if (url.charAt(url.length() - 1) != '/') {
url.append('/');
}
url.append(DATABASE_NAME);
// no need for validation query, saves some work
url.append(";AUTO_RECONNECT=TRUE;CACHE_SIZE=131072;CACHE_TYPE=TQ");
source.setURL((url.toString()));
source.setUser("sa");
source.setPassword("");
assert directory.equals(getDirectory(source)) : url;
}
/*
* If the temporary directory do not exists or can't be created, lets the 'database'
* attribute unset. If the user do not set it explicitly (through JNDI or by overrding
* this method), an exception will be thrown when 'createBackingStore()' will be invoked.
*/
source.setUser("SA"); // System administrator. No password.
return source;
}
public DLNAMediaDatabase(String name) {
dbName = name;
File profileDirectory = new File(configuration.getProfileDirectory());
dbDir =
new File(
profileDirectory.isDirectory() ? configuration.getProfileDirectory() : null,
"database")
.getAbsolutePath();
url = Constants.START_URL + dbDir + File.separator + dbName;
LOGGER.debug("Using database URL: " + url);
LOGGER.info("Using database located at: " + dbDir);
try {
Class.forName("org.h2.Driver");
} catch (ClassNotFoundException e) {
LOGGER.error(null, e);
}
JdbcDataSource ds = new JdbcDataSource();
ds.setURL(url);
ds.setUser("sa");
ds.setPassword("");
cp = JdbcConnectionPool.create(ds);
}
@Bean(name = "dataSource")
public DataSource getDataSource() {
JdbcDataSource dataSource = new JdbcDataSource();
dataSource.setURL("jdbc:h2:mem:hackathon-admin;DB_CLOSE_DELAY=-1");
return dataSource;
}
public static void main(String[] args) throws Exception {
JdbcDataSource WorkingtigerDs = new JdbcDataSource();
WorkingtigerDs.setURL(
"jdbc:h2:C:\\Users\\Gateway\\Documents\\GitHub\\JGeocoder\\local\\street_data.h2");
final Logger logger = Logger.getLogger(import_tiger_main.class);
String csvFile;
Scanner inFile = null;
String[] holdFileLine = null;
logger.info("Initialized import_tiger_main");
csvFile =
("C:\\Users\\Gateway\\Documents\\GitHub\\JGeocoder\\jgeocoder\\src\\main\\java\\net\\sourceforge\\jgeocoder\\ConvertedDate.csv");
inFile = new Scanner(new File(csvFile));
int count = 0;
String fedirp = null;
String fedirs = null;
String fetype = null;
String msg = null;
QueryRunner run = new QueryRunner();
try {
Connection conn = WorkingtigerDs.getConnection();
int updates = run.update(conn, "DROP TABLE IF EXISTS TIGER_NY;");
updates =
run.update(
conn,
"create table TIGER_NY ( TLID numeric not null, TFIDL varchar(255),"
+ "TFIDR varchar(255), ARIDL varchar(255),"
+ "ARIDR varchar(255),LINEARID varchar(255),FEDIRP varchar(255),FENAME varchar(255),"
+ "FETYPE varchar(255), FEDIRS varchar(255), FULLNAME varchar(255), "
+ "FRADDL varchar(255), TOADDL varchar(255),FRADDR varchar(255), "
+ "TOADDR varchar(255), ZIPL varchar(255),ZIPR varchar (255),"
+ " EDGEMTFCC varchar(255), ROADMTFCC varchar(255), PARITYL varchar(255), "
+ "PARITYR varchar(255), PLUS4L varchar(255),"
+ "PLUS4R varchar(255), LFROMTYP varchar(255), LTOTYP "
+ "varchar(255), RFROMTYP varchar(255),"
+ "RTOTYP varchar(255), OFFSETL varchar(255), OFFSETR "
+ "varchar(255), BBOX text,NUMPARTS text,"
+ "SHAPETYPE numeric, LATLONGPAIRS text);");
// numeric
while (inFile.hasNextLine()) {
String line = inFile.nextLine();
count++;
holdFileLine = line.split(",");
if (count % 1000 == 0) {
logger.info(count + "records inserted.");
}
Map<AddressComponent, String> m = null;
try {
if (holdFileLine[9] != null) {
if (holdFileLine[6].equals("Federal Hill Rd II")) {
msg = "Parser does not handle: " + holdFileLine[6] + " . Will return null pointer.";
logger.error(msg);
continue;
}
if (holdFileLine[6].equals("Black Bridge Rd II")) {
msg = "Parser does not handle: " + holdFileLine[6] + " . Will return null pointer.";
logger.error(msg);
continue;
}
m = AddressParser.parseAddress("103 " + holdFileLine[6] + " " + holdFileLine[11]);
} else {
m = AddressParser.parseAddress("103 " + holdFileLine[6] + " " + holdFileLine[12]);
}
if (m == null) {
msg = "Error parsing: " + line + ".";
logger.error(msg);
continue;
}
m = AddressStandardizer.normalizeParsedAddress(m);
if (m.get(AddressComponent.PREDIR) == null) {
fedirp = null;
} else {
fedirp = m.get(AddressComponent.PREDIR);
}
if (m.get(AddressComponent.POSTDIR) == "null") {
fedirs = null;
} else {
fedirs = m.get(AddressComponent.PREDIR);
}
if (m.get(AddressComponent.TYPE) == "null") {
fetype = null;
msg = "FETYPE is null for: " + line + ".";
logger.warn(msg);
} else {
fetype = m.get(AddressComponent.TYPE);
}
String[] temp = new String[holdFileLine.length + 4];
for (int i = 0; i < holdFileLine.length - 1; i++) {
temp[i] = holdFileLine[i];
}
String street = m.get(AddressComponent.STREET);
temp[temp.length - 4] = fedirp;
temp[temp.length - 3] = street;
temp[temp.length - 2] = fetype;
temp[temp.length - 1] = fedirs;
holdFileLine = temp;
if (!conn.isValid(1)) {
conn.close();
conn = WorkingtigerDs.getConnection();
}
updates =
run.update(
conn,
"insert into TIGER_NY( TLID,TFIDL,TFIDR,ARIDL,ARIDR,LINEARID,"
+ "FULLNAME,FRADDL,TOADDL,FRADDR,TOADDR,ZIPL,ZIPR,EDGEMTFCC, ROADMTFCC,"
+ "PARITYL,PARITYR,"
+ "PLUS4L,PLUS4R,"
+ "LFROMTYP,LTOTYP,RFROMTYP,RTOTYP,OFFSETL,OFFSETR,BBOX,NUMPARTS,SHAPETYPE,"
+ "LATLONGPAIRS,FEDIRP,FENAME,FETYPE,FEDIRS) "
+ " values (?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,"
+ "?)",
holdFileLine);
} catch (SQLException sqle) {
logger.error(sqle);
throw sqle;
}
}
if (!conn.isValid(1)) {
conn.close();
conn = WorkingtigerDs.getConnection();
}
System.out.println("creating indicies on TIGER_NY");
run.update(conn, "create index IDX0_TIGER_NY on TIGER_NY(tlid)");
run.update(conn, "create index IDX1_TIGER_NY on TIGER_NY(fename)");
run.update(conn, "create index IDX2_TIGER_NY on TIGER_NY(fraddL)");
run.update(conn, "create index IDX3_TIGER_NY on TIGER_NY(toaddL)");
run.update(conn, "create index IDX4_TIGER_NY on TIGER_NY(fraddR)");
run.update(conn, "create index IDX5_TIGER_NY on TIGER_NY(toaddR)");
run.update(conn, "create index IDX6_TIGER_NY on TIGER_NY(zipL)");
run.update(conn, "create index IDX7_TIGER_NY on TIGER_NY(zipR)");
conn.close();
} catch (SQLException sqle) {
logger.error(sqle);
throw sqle;
}
}
public static DataSource createInternalDS(String guid) {
JdbcDataSource h2 = new JdbcDataSource();
h2.setURL("jdbc:h2:mem:" + new GUID().toString() + ";DB_CLOSE_DELAY=-1");
h2.setUser("sa");
return h2;
}
