svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.sqlite / org.gvsig.sqlite.provider / src / test / java / org / gvsig / sqlite / dal / RunSQL.java @ 47579
History | View | Annotate | Download (30.3 KB)
| 1 |
package org.gvsig.sqlite.dal; |
|---|---|
| 2 |
|
| 3 |
import java.io.File; |
| 4 |
import java.sql.Connection; |
| 5 |
import java.sql.ResultSet; |
| 6 |
import java.sql.ResultSetMetaData; |
| 7 |
import java.sql.SQLException; |
| 8 |
import java.sql.Statement; |
| 9 |
import java.util.ArrayList; |
| 10 |
import java.util.List; |
| 11 |
import java.util.Objects; |
| 12 |
import org.apache.commons.io.FilenameUtils; |
| 13 |
import org.apache.commons.lang3.StringUtils; |
| 14 |
import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_CONFIGURATION_NAME; |
| 15 |
import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_CONFIGURATION_VALUE; |
| 16 |
import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_RESOURCES_NAME; |
| 17 |
import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_RESOURCES_RESOURCE; |
| 18 |
import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_CONFIGURATION_NAME; |
| 19 |
import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_RESOURCES_NAME; |
| 20 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCDriverClassNotFoundException; |
| 21 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
| 22 |
import org.gvsig.sqlite.dal.functions.Functions; |
| 23 |
import org.gvsig.sqlite.dal.geopackage.GeopackageUtils; |
| 24 |
import org.gvsig.tools.library.impl.DefaultLibrariesInitializer; |
| 25 |
import org.slf4j.Logger; |
| 26 |
import org.slf4j.LoggerFactory; |
| 27 |
import org.sqlite.SQLiteConfig; |
| 28 |
import org.sqlite.SQLiteConnection; |
| 29 |
import org.sqlite.SQLiteOpenMode; |
| 30 |
|
| 31 |
public class RunSQL { |
| 32 |
|
| 33 |
private static final Logger LOGGER = LoggerFactory.getLogger(RunSQL.class); |
| 34 |
|
| 35 |
public static final String DBNAME = "testCreate"; |
| 36 |
|
| 37 |
public static void main(String[] args) throws Exception { |
| 38 |
new DefaultLibrariesInitializer().fullInitialize();
|
| 39 |
|
| 40 |
//Modificar estas tres l?neas para cada test
|
| 41 |
//Test date
|
| 42 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\" FROM \"test\" WHERE (((\"test\".\"Time\" > ('01:02:03')) AND (\"test\".\"Time\" < ('20:52:55'))) OR ((\"test\".\"Date\" > ('2019-08-01')) AND (\"test\".\"Date\" < ('2020-02-23')))) ORDER BY \"test\".\"ID\" ASC";
|
| 43 |
|
| 44 |
//Test drop table (Sin ejecutar)
|
| 45 |
// String sql = "DELETE FROM gpkg_geometry_columns WHERE table_name = \"test\"";
|
| 46 |
// String sql = "DELETE FROM \"main\".\"gpkg_contents\" WHERE \"identifier\" = 'test'";
|
| 47 |
// String sql = "DELETE FROM \"main\".\"gpkg_extensions\" WHERE \"table_name\" = 'test'";
|
| 48 |
// String sql = "DROP TABLE \"test\"";
|
| 49 |
|
| 50 |
//Test fetch feature type by reference
|
| 51 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\" FROM \"test\" WHERE ( (\"test\".\"ID\") = (4) ) LIMIT 1";
|
| 52 |
|
| 53 |
//FetchFeatureType first row SQL
|
| 54 |
// String sql = "SELECT * FROM \"test\" LIMIT 0";
|
| 55 |
|
| 56 |
//Test fetch feature type (CHUNGO REVISAR)
|
| 57 |
// String sql = "SELECT \n" +
|
| 58 |
// " \"COLUMN_NAME\", \"CONSTRAINT_TYPE\" \n" +
|
| 59 |
// "FROM INFORMATION_SCHEMA.table_constraints t_cons inner join INFORMATION_SCHEMA.key_column_usage c on c.constraint_catalog = t_cons.constraint_catalog and c.table_schema = t_cons.table_schema and c.table_name = t_cons.table_name and c.constraint_name = t_cons.constraint_name \n" +
|
| 60 |
// "WHERE (( (c.TABLE_NAME) LIKE ('test') ) AND ( (\"CONSTRAINT_TYPE\") = ('PRIMARY KEY') ))";
|
| 61 |
|
| 62 |
|
| 63 |
//Test Subselect (CHUNGO REVISAR)
|
| 64 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", "
|
| 65 |
// + "\"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", "
|
| 66 |
// + "\"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", "
|
| 67 |
// + "\"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\", "
|
| 68 |
// + "EXISTS(( SELECT \"countries\".\"ISO_A2\" "
|
| 69 |
// + "FROM \"countries\" "
|
| 70 |
// + "WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) AS \"EXISTS62a964cd7bc24f409b97c03b9170408d\" "
|
| 71 |
// + "FROM \"test\" "
|
| 72 |
// + "WHERE EXISTS(( SELECT \"countries\".\"ISO_A2\" "
|
| 73 |
// + "FROM \"countries\" "
|
| 74 |
// + "WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) "
|
| 75 |
// + "ORDER BY \"test\".\"ID\" ASC";
|
| 76 |
|
| 77 |
|
| 78 |
//Test STDistance (He a?adido la funcion ST_DISTANCE)
|
| 79 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\" FROM \"test\" WHERE ( ((\"ID\" IN (SELECT \"rtree_test_Geometry\".\"id\" FROM \"rtree_test_Geometry\" WHERE \"rtree_test_Geometry\".\"minx\" <= (1.0) AND \"rtree_test_Geometry\".\"miny\" <= (41.0) AND \"rtree_test_Geometry\".\"maxx\" >= (-1.0) AND \"rtree_test_Geometry\".\"maxy\" >= (39.0)))) AND ST_Intersects((ST_GeomFromWKB((X'000000000300000001000000213ff000000000000040440000000000003fef6297cff75cb04043e70747c3965a3fed906bcf328d464043cf043ab2a2ad3fea9b66290ea1a34043b8e31319465d3fe6a09e667f3bcd4043a57d866603113fe1c73b39ae68c940439592675bc5793fd87de2a6aea964404389be50c335cb3fc8f8b83c69a60d40438275a0c0228d3c91a62633145c074043800000000000bfc8f8b83c69a60840438275a0c0228dbfd87de2a6aea962404389be50c335cbbfe1c73b39ae68c640439592675bc579bfe6a09e667f3bcc4043a57d86660311bfea9b66290ea1a44043b8e31319465dbfed906bcf328d474043cf043ab2a2adbfef6297cff75cb14043e70747c3965abff00000000000004044000000000000bfef6297cff75cae404418f8b83c69a6bfed906bcf328d42404430fbc54d5d53bfea9b66290ea19c4044471cece6b9a3bfe6a09e667f3bc240445a827999fcefbfe1c73b39ae68bb40446a6d98a43a87bfd87de2a6aea94140447641af3cca35bfc8f8b83c69a5bb40447d8a5f3fdd733ce6586c6b36175f40448000000000003fc8f8b83c69a66a40447d8a5f3fdd733fd87de2a6aea99340447641af3cca353fe1c73b39ae68e040446a6d98a43a863fe6a09e667f3be240445a827999fcef3fea9b66290ea1b54044471cece6b9a33fed906bcf328d53404430fbc54d5d523fef6297cff75cb7404418f8b83c69a53ff00000000000004044000000000000'), 4326)),(\"test\".\"Geometry\") )) ORDER BY ST_Distance(ST_GeomFromWKB((X'000000000300000001000000213ff000000000000040440000000000003fef6297cff75cb04043e70747c3965a3fed906bcf328d464043cf043ab2a2ad3fea9b66290ea1a34043b8e31319465d3fe6a09e667f3bcd4043a57d866603113fe1c73b39ae68c940439592675bc5793fd87de2a6aea964404389be50c335cb3fc8f8b83c69a60d40438275a0c0228d3c91a62633145c074043800000000000bfc8f8b83c69a60840438275a0c0228dbfd87de2a6aea962404389be50c335cbbfe1c73b39ae68c640439592675bc579bfe6a09e667f3bcc4043a57d86660311bfea9b66290ea1a44043b8e31319465dbfed906bcf328d474043cf043ab2a2adbfef6297cff75cb14043e70747c3965abff00000000000004044000000000000bfef6297cff75cae404418f8b83c69a6bfed906bcf328d42404430fbc54d5d53bfea9b66290ea19c4044471cece6b9a3bfe6a09e667f3bc240445a827999fcefbfe1c73b39ae68bb40446a6d98a43a87bfd87de2a6aea94140447641af3cca35bfc8f8b83c69a5bb40447d8a5f3fdd733ce6586c6b36175f40448000000000003fc8f8b83c69a66a40447d8a5f3fdd733fd87de2a6aea99340447641af3cca353fe1c73b39ae68e040446a6d98a43a863fe6a09e667f3be240445a827999fcef3fea9b66290ea1b54044471cece6b9a33fed906bcf328d53404430fbc54d5d523fef6297cff75cb7404418f8b83c69a53ff00000000000004044000000000000'), 4326), \"Geometry\") ASC NULLS LAST, \"test\".\"ID\" ASC LIMIT 1";
|
| 80 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\" FROM \"test\" WHERE ST_Intersects((ST_GeomFromWKB((X'000000000300000001000000213ff000000000000040440000000000003fef6297cff75cb04043e70747c3965a3fed906bcf328d464043cf043ab2a2ad3fea9b66290ea1a34043b8e31319465d3fe6a09e667f3bcd4043a57d866603113fe1c73b39ae68c940439592675bc5793fd87de2a6aea964404389be50c335cb3fc8f8b83c69a60d40438275a0c0228d3c91a62633145c074043800000000000bfc8f8b83c69a60840438275a0c0228dbfd87de2a6aea962404389be50c335cbbfe1c73b39ae68c640439592675bc579bfe6a09e667f3bcc4043a57d86660311bfea9b66290ea1a44043b8e31319465dbfed906bcf328d474043cf043ab2a2adbfef6297cff75cb14043e70747c3965abff00000000000004044000000000000bfef6297cff75cae404418f8b83c69a6bfed906bcf328d42404430fbc54d5d53bfea9b66290ea19c4044471cece6b9a3bfe6a09e667f3bc240445a827999fcefbfe1c73b39ae68bb40446a6d98a43a87bfd87de2a6aea94140447641af3cca35bfc8f8b83c69a5bb40447d8a5f3fdd733ce6586c6b36175f40448000000000003fc8f8b83c69a66a40447d8a5f3fdd733fd87de2a6aea99340447641af3cca353fe1c73b39ae68e040446a6d98a43a863fe6a09e667f3be240445a827999fcef3fea9b66290ea1b54044471cece6b9a33fed906bcf328d53404430fbc54d5d523fef6297cff75cb7404418f8b83c69a53ff00000000000004044000000000000'), 4326)),(\"test\".\"Geometry\")) ORDER BY ST_Distance(ST_GeomFromWKB((X'000000000300000001000000213ff000000000000040440000000000003fef6297cff75cb04043e70747c3965a3fed906bcf328d464043cf043ab2a2ad3fea9b66290ea1a34043b8e31319465d3fe6a09e667f3bcd4043a57d866603113fe1c73b39ae68c940439592675bc5793fd87de2a6aea964404389be50c335cb3fc8f8b83c69a60d40438275a0c0228d3c91a62633145c074043800000000000bfc8f8b83c69a60840438275a0c0228dbfd87de2a6aea962404389be50c335cbbfe1c73b39ae68c640439592675bc579bfe6a09e667f3bcc4043a57d86660311bfea9b66290ea1a44043b8e31319465dbfed906bcf328d474043cf043ab2a2adbfef6297cff75cb14043e70747c3965abff00000000000004044000000000000bfef6297cff75cae404418f8b83c69a6bfed906bcf328d42404430fbc54d5d53bfea9b66290ea19c4044471cece6b9a3bfe6a09e667f3bc240445a827999fcefbfe1c73b39ae68bb40446a6d98a43a87bfd87de2a6aea94140447641af3cca35bfc8f8b83c69a5bb40447d8a5f3fdd733ce6586c6b36175f40448000000000003fc8f8b83c69a66a40447d8a5f3fdd733fd87de2a6aea99340447641af3cca353fe1c73b39ae68e040446a6d98a43a863fe6a09e667f3be240445a827999fcef3fea9b66290ea1b54044471cece6b9a33fed906bcf328d53404430fbc54d5d523fef6297cff75cb7404418f8b83c69a53ff00000000000004044000000000000'), 4326), \"Geometry\") ASC NULLS LAST, \"test\".\"ID\" ASC LIMIT 1";
|
| 81 |
|
| 82 |
//Test Perform changes
|
| 83 |
// List<String> sqls = new ArrayList();
|
| 84 |
//###### SQL[insert]:
|
| 85 |
// sqls.add("INSERT INTO \"test\" ( \"ID\", \"Byte\", \"Bool1\", \"Long\", \"Timestamp\", \"Date\", \"Time\", \"Bool2\", \"String\", \"Bool3\", \"Double\", \"Bool4\", \"Float\", \"Bool5\", \"Decimal\", \"Geometry\" ) VALUES ( 30, 30, TRUE, 30000, CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, TRUE, 'Treinta', FALSE, 30.3030, TRUE, 30.3030, TRUE, 30.303, ST_GeomFromWKB((X'000000000300000001000000213ff000000000000040440000000000003fef6297cff75cb04043e70747c3965a3fed906bcf328d464043cf043ab2a2ad3fea9b66290ea1a34043b8e31319465d3fe6a09e667f3bcd4043a57d866603113fe1c73b39ae68c940439592675bc5793fd87de2a6aea964404389be50c335cb3fc8f8b83c69a60d40438275a0c0228d3c91a62633145c074043800000000000bfc8f8b83c69a60840438275a0c0228dbfd87de2a6aea962404389be50c335cbbfe1c73b39ae68c640439592675bc579bfe6a09e667f3bcc4043a57d86660311bfea9b66290ea1a44043b8e31319465dbfed906bcf328d474043cf043ab2a2adbfef6297cff75cb14043e70747c3965abff00000000000004044000000000000bfef6297cff75cae404418f8b83c69a6bfed906bcf328d42404430fbc54d5d53bfea9b66290ea19c4044471cece6b9a3bfe6a09e667f3bc240445a827999fcefbfe1c73b39ae68bb40446a6d98a43a87bfd87de2a6aea94140447641af3cca35bfc8f8b83c69a5bb40447d8a5f3fdd733ce6586c6b36175f40448000000000003fc8f8b83c69a66a40447d8a5f3fdd733fd87de2a6aea99340447641af3cca353fe1c73b39ae68e040446a6d98a43a863fe6a09e667f3be240445a827999fcef3fea9b66290ea1b54044471cece6b9a33fed906bcf328d53404430fbc54d5d523fef6297cff75cb7404418f8b83c69a53ff00000000000004044000000000000'), 4326) )");
|
| 86 |
//###### SQL[delete]:
|
| 87 |
// sqls.add("DELETE FROM \"test\" WHERE ( (\"ID\") = (30) )");
|
| 88 |
//###### SQL[update]:
|
| 89 |
// sqls.add("UPDATE \"test\" SET \"Byte\" = 31, \"Bool1\" = FALSE, \"Long\" = 313131, \"Timestamp\" = CURRENT_TIMESTAMP, \"Date\" = CURRENT_DATE, \"Time\" = CURRENT_TIME, \"Bool2\" = FALSE, \"String\" = 'Treinta y uno', \"Bool3\" = FALSE, \"Double\" = 31.3131, \"Bool4\" = FALSE, \"Float\" = 31.3131, \"Bool5\" = FALSE, \"Decimal\" = 31.313, \"Geometry\" = ST_GeomFromWKB((X'000000000300000001000000213ff000000000000040440000000000003fef6297cff75cb04043e70747c3965a3fed906bcf328d464043cf043ab2a2ad3fea9b66290ea1a34043b8e31319465d3fe6a09e667f3bcd4043a57d866603113fe1c73b39ae68c940439592675bc5793fd87de2a6aea964404389be50c335cb3fc8f8b83c69a60d40438275a0c0228d3c91a62633145c074043800000000000bfc8f8b83c69a60840438275a0c0228dbfd87de2a6aea962404389be50c335cbbfe1c73b39ae68c640439592675bc579bfe6a09e667f3bcc4043a57d86660311bfea9b66290ea1a44043b8e31319465dbfed906bcf328d474043cf043ab2a2adbfef6297cff75cb14043e70747c3965abff00000000000004044000000000000bfef6297cff75cae404418f8b83c69a6bfed906bcf328d42404430fbc54d5d53bfea9b66290ea19c4044471cece6b9a3bfe6a09e667f3bc240445a827999fcefbfe1c73b39ae68bb40446a6d98a43a87bfd87de2a6aea94140447641af3cca35bfc8f8b83c69a5bb40447d8a5f3fdd733ce6586c6b36175f40448000000000003fc8f8b83c69a66a40447d8a5f3fdd733fd87de2a6aea99340447641af3cca353fe1c73b39ae68e040446a6d98a43a863fe6a09e667f3be240445a827999fcef3fea9b66290ea1b54044471cece6b9a33fed906bcf328d53404430fbc54d5d523fef6297cff75cb7404418f8b83c69a53ff00000000000004044000000000000'), 4326) WHERE ( (\"ID\") = (30) )");
|
| 90 |
//###### SQL[updatetable 0]:
|
| 91 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Byte10\" TINYINT DEFAULT NULL NULL");
|
| 92 |
// //###### SQL[updatetable 1]:
|
| 93 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Bool101\" BOOLEAN DEFAULT NULL NULL");
|
| 94 |
// //###### SQL[updatetable 2]:
|
| 95 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Long10\" BIGINT DEFAULT NULL NULL");
|
| 96 |
// //###### SQL[updatetable 3]:
|
| 97 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Timestamp10\" DATETIME DEFAULT NULL NULL");
|
| 98 |
// //###### SQL[updatetable 4]:
|
| 99 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Date10\" DATE DEFAULT NULL NULL");
|
| 100 |
// //###### SQL[updatetable 5]:
|
| 101 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Time10\" TIME DEFAULT NULL NULL");
|
| 102 |
// //###### SQL[updatetable 6]:
|
| 103 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Bool102\" BOOLEAN DEFAULT NULL NULL");
|
| 104 |
// //###### SQL[updatetable 7]:
|
| 105 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"String10\" VARCHAR(30) DEFAULT NULL NULL");
|
| 106 |
// //###### SQL[updatetable 8]:
|
| 107 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Bool103\" BOOLEAN DEFAULT NULL NULL");
|
| 108 |
// //###### SQL[updatetable 9]:
|
| 109 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Double10\" DOUBLE PRECISION DEFAULT NULL NULL");
|
| 110 |
// //###### SQL[updatetable 10]:
|
| 111 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Bool104\" BOOLEAN DEFAULT NULL NULL");
|
| 112 |
// //###### SQL[updatetable 11]:
|
| 113 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Float10\" FLOAT DEFAULT NULL NULL");
|
| 114 |
// //###### SQL[updatetable 12]:
|
| 115 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Bool105\" BOOLEAN DEFAULT NULL NULL");
|
| 116 |
// //###### SQL[updatetable 13]:
|
| 117 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Decimal10\" DECIMAL(6,3) DEFAULT NULL NULL");
|
| 118 |
// //###### SQL[updatetable 14]:
|
| 119 |
// sqls.add("ALTER TABLE \"test\" ADD COLUMN \"Geometry10\" BLOB DEFAULT NULL NULL");
|
| 120 |
//
|
| 121 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Byte10\"");
|
| 122 |
// //###### SQL[updatetable 1]:
|
| 123 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Bool101\"");
|
| 124 |
// //###### SQL[updatetable 2]:
|
| 125 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Long10\"");
|
| 126 |
// //###### SQL[updatetable 3]:
|
| 127 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Timestamp10\"");
|
| 128 |
// //###### SQL[updatetable 4]:
|
| 129 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Date10\"");
|
| 130 |
// //###### SQL[updatetable 5]:
|
| 131 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Time10\"");
|
| 132 |
// //###### SQL[updatetable 6]:
|
| 133 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Bool102\"");
|
| 134 |
// //###### SQL[updatetable 7]:
|
| 135 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"String10\"");
|
| 136 |
// //###### SQL[updatetable 8]:
|
| 137 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Bool103\"");
|
| 138 |
// //###### SQL[updatetable 9]:
|
| 139 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Double10\"");
|
| 140 |
// //###### SQL[updatetable 10]:
|
| 141 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Bool104\"");
|
| 142 |
// //###### SQL[updatetable 11]:
|
| 143 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Float10\"");
|
| 144 |
// //###### SQL[updatetable 12]:
|
| 145 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Bool105\"");
|
| 146 |
// //###### SQL[updatetable 13]:
|
| 147 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Decimal10\"");
|
| 148 |
// //###### SQL[updatetable 14]:
|
| 149 |
// sqls.add("ALTER TABLE \"test\" DROP COLUMN \"Geometry10\"");
|
| 150 |
|
| 151 |
|
| 152 |
//ResultSetForSetProvider
|
| 153 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\" FROM \"test\" ORDER BY \"test\".\"ID\" ASC";
|
| 154 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\", (\"test\".\"ID\" * 2) AS \"Compu1\" FROM \"test\" ORDER BY \"test\".\"ID\" ASC";
|
| 155 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\", (\"test\".\"ID\" * 2) AS \"Compu1\", ((\"test\".\"Long\" + 10) + (\"test\".\"ID\" * 2)) AS \"Compu2\" FROM \"test\" ORDER BY \"test\".\"ID\" ASC";
|
| 156 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\", (\"test\".\"ID\" * 2) AS \"Compu1\", ((\"test\".\"Long\" + 10) + (\"test\".\"ID\" * 2)) AS \"Extra1\" FROM \"test\" ORDER BY \"test\".\"ID\" ASC";
|
| 157 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\", (\"test\".\"ID\" * 2) AS \"Extra1\", ((\"test\".\"Long\" + 10) + (\"test\".\"ID\" * 2)) AS \"Extra2\" FROM \"test\" ORDER BY \"test\".\"ID\" ASC";
|
| 158 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\", (\"test\".\"ID\" * 2) AS \"Extra1\", ((\"test\".\"Long\" + 10) + (\"test\".\"ID\" * 2)) AS \"Extra2\" FROM \"test\" ORDER BY \"test\".\"ID\" ASC";
|
| 159 |
// String sql = "SELECT MIN(\"test\".\"ID\") AS \"ID\", MAX(\"test\".\"Byte\") AS \"Byte\", NULL AS \"Bool1\", \"test\".\"Long\", NULL AS \"Timestamp\", NULL AS \"Date\", NULL AS \"Time\", NULL AS \"Bool2\", NULL AS \"String\", NULL AS \"Bool3\", SUM(\"test\".\"Double\") AS \"Double\", NULL AS \"Bool4\", NULL AS \"Float\", NULL AS \"Bool5\", NULL AS \"Decimal\", NULL AS \"Geometry\" FROM \"test\" GROUP BY \"test\".\"Long\" ORDER BY \"test\".\"Long\" ASC NULLS LAST";
|
| 160 |
// String sql = "SELECT MIN(\"test\".\"ID\") AS \"ID\", MAX(\"test\".\"Byte\") AS \"Byte\", NULL AS \"Bool1\", NULL AS \"Long\", NULL AS \"Timestamp\", NULL AS \"Date\", NULL AS \"Time\", NULL AS \"Bool2\", NULL AS \"String\", NULL AS \"Bool3\", SUM(\"test\".\"Double\") AS \"Double\", NULL AS \"Bool4\", NULL AS \"Float\", NULL AS \"Bool5\", NULL AS \"Decimal\", NULL AS \"Geometry\", SUM((\"test\".\"Long\" + 10)) AS \"Extra1\", NULL AS \"Extra2\" FROM \"test\" ORDER BY MIN(\"test\".\"ID\") ASC, SUM((\"Long\" + 10)) ASC NULLS LAST";
|
| 161 |
// String sql = "SELECT MIN(\"test\".\"ID\") AS \"ID\", MAX(\"test\".\"Byte\") AS \"Byte\", NULL AS \"Bool1\", \"test\".\"Long\", NULL AS \"Timestamp\", NULL AS \"Date\", NULL AS \"Time\", NULL AS \"Bool2\", NULL AS \"String\", NULL AS \"Bool3\", SUM(\"test\".\"Double\") AS \"Double\", NULL AS \"Bool4\", NULL AS \"Float\", NULL AS \"Bool5\", NULL AS \"Decimal\", NULL AS \"Geometry\", (\"test\".\"ID\" * 2) AS \"Compu1\", SUM((\"test\".\"Long\" + 300)) AS \"Compu2\", SUM(1) AS \"Compu3\", ((\"test\".\"Long\" + 10) + (\"test\".\"ID\" * 2)) AS \"Extra1\", SUM(((20 + \"test\".\"Byte\") + (\"test\".\"ID\" * 2))) AS \"Extra2\" FROM \"test\" GROUP BY \"test\".\"Long\", ((\"test\".\"Long\" + 10) + (\"test\".\"ID\" * 2)), (\"test\".\"ID\" * 2) ORDER BY \"test\".\"Long\" ASC NULLS LAST, ((\"test\".\"Long\" + 10) + (\"test\".\"ID\" * 2)) ASC NULLS LAST, (\"test\".\"ID\" * 2) ASC NULLS LAST";
|
| 162 |
// String sql = "SELECT MIN(\"test\".\"ID\") AS \"ID\", MAX(\"test\".\"Byte\") AS \"Byte\", NULL AS \"Bool1\", \"test\".\"Long\", NULL AS \"Timestamp\", NULL AS \"Date\", NULL AS \"Time\", NULL AS \"Bool2\", NULL AS \"String\", NULL AS \"Bool3\", SUM(\"test\".\"Double\") AS \"Double\", NULL AS \"Bool4\", NULL AS \"Float\", NULL AS \"Bool5\", NULL AS \"Decimal\", NULL AS \"Geometry\", (\"test\".\"ID\" * 2) AS \"Compu1\", SUM((\"test\".\"Long\" + 300)) AS \"Compu2\", SUM(1) AS \"Compu3\", ((\"test\".\"Long\" + 10) + (\"test\".\"ID\" * 2)) AS \"Extra1\", SUM(((20 + \"test\".\"Byte\") + (\"test\".\"ID\" * 2))) AS \"Extra2\", NULL AS \"Extra3\" FROM \"test\" GROUP BY \"test\".\"Long\", ((\"test\".\"Long\" + 10) + (\"test\".\"ID\" * 2)), (\"test\".\"ID\" * 2) ORDER BY ((\"Long\" + 10) + (\"ID\" * 2)) ASC NULLS LAST, SUM(((20 + \"Byte\") + (\"ID\" * 2))) ASC NULLS LAST";
|
| 163 |
|
| 164 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\", EXISTS(( SELECT \"countries\".\"ISO_A2\" FROM \"countries\" WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) AS \"EXISTS62a964cd7bc24f409b97c03b9170408d\" FROM \"test\" WHERE EXISTS(( SELECT \"countries\".\"ISO_A2\" FROM \"countries\" WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) ORDER BY \"test\".\"ID\" ASC"; //*
|
| 165 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\", EXISTS(( SELECT \"test\".\"Long\" FROM \"countries\" WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) AS \"EXISTS62a964cd7bc24f409b97c03b9170408d\" FROM \"test\" WHERE EXISTS(( SELECT \"test\".\"Long\" FROM \"countries\" WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) ORDER BY \"test\".\"ID\" ASC"; //*
|
| 166 |
// String sql = "SELECT MIN(\"test\".\"ID\") AS \"ID\", MAX(\"test\".\"Byte\") AS \"Byte\", NULL AS \"Bool1\", \"test\".\"Long\", NULL AS \"Timestamp\", NULL AS \"Date\", NULL AS \"Time\", NULL AS \"Bool2\", NULL AS \"String\", NULL AS \"Bool3\", SUM(\"test\".\"Double\") AS \"Double\", NULL AS \"Bool4\", NULL AS \"Float\", NULL AS \"Bool5\", NULL AS \"Decimal\", NULL AS \"Geometry\" FROM \"test\" WHERE EXISTS(( SELECT \"countries\".\"ISO_A2\" FROM \"countries\" WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) GROUP BY \"test\".\"Long\" ORDER BY \"test\".\"Long\" ASC NULLS LAST"; //*
|
| 167 |
|
| 168 |
// String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\" FROM \"test\" ORDER BY \"test\".\"ID\" ASC";
|
| 169 |
|
| 170 |
String sql = ""; |
| 171 |
|
| 172 |
|
| 173 |
|
| 174 |
String dbPath = "/home/fdiaz/projects/gvSIG/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.sqlite/org.gvsig.sqlite.provider/target/test-dbs/"; |
| 175 |
String dbName = "testCreate-1699008328746-001.gpkg"; |
| 176 |
|
| 177 |
System.out.println(dbPath+dbName);
|
| 178 |
|
| 179 |
// for (String sql : sqls) {
|
| 180 |
|
| 181 |
|
| 182 |
String table = "testCreateTarget1"; |
| 183 |
sql = sql.replaceAll("_test_", "_"+table+"_"); |
| 184 |
sql = sql.replaceAll("\"test\"", "\""+table+"\""); |
| 185 |
|
| 186 |
System.out.println("SQL " + sql); |
| 187 |
|
| 188 |
Connection conn = getConnection(dbPath + dbName);
|
| 189 |
Statement stmt = conn.createStatement();
|
| 190 |
stmt.execute(sql); |
| 191 |
ResultSet rs3 = stmt.executeQuery(sql);
|
| 192 |
ResultSetMetaData rsMetadata = rs3.getMetaData();
|
| 193 |
int columnCount = rsMetadata.getColumnCount();
|
| 194 |
int[] colsizes = new int[columnCount + 1]; |
| 195 |
int recordsCount = 0; |
| 196 |
for (int i = 1; i <= columnCount; i++) { |
| 197 |
colsizes[i] = rsMetadata.getColumnName(i).length() + 1;
|
| 198 |
} |
| 199 |
while (rs3.next()) {
|
| 200 |
for (int i = 1; i <= columnCount; i++) { |
| 201 |
int l = Objects.toString(rs3.getObject(i)).length() + 1; |
| 202 |
if (l > colsizes[i]) {
|
| 203 |
colsizes[i] = l; |
| 204 |
} |
| 205 |
} |
| 206 |
recordsCount++; |
| 207 |
} |
| 208 |
rs3.close(); |
| 209 |
System.out.println("Records " + recordsCount); |
| 210 |
|
| 211 |
ResultSet rs = stmt.executeQuery(sql);
|
| 212 |
rsMetadata = rs.getMetaData(); |
| 213 |
|
| 214 |
for (int i = 1; i <= columnCount; i++) { |
| 215 |
System.out.print(StringUtils.rightPad(rsMetadata.getColumnName(i), colsizes[i]));
|
| 216 |
} |
| 217 |
System.out.println();
|
| 218 |
for (int i = 1; i <= columnCount; i++) { |
| 219 |
System.out.print(StringUtils.repeat("-", colsizes[i] - 1) + " "); |
| 220 |
} |
| 221 |
System.out.println();
|
| 222 |
while (rs.next()) {
|
| 223 |
for (int i = 1; i <= columnCount; i++) { |
| 224 |
System.out.print(StringUtils.rightPad(Objects.toString(rs.getObject(i)), colsizes[i]));
|
| 225 |
} |
| 226 |
System.out.println();
|
| 227 |
} |
| 228 |
rs.close(); |
| 229 |
System.out.println();
|
| 230 |
JDBCUtils.close(conn); |
| 231 |
// }
|
| 232 |
} |
| 233 |
|
| 234 |
public static Connection getConnection(String fName) throws SQLException { |
| 235 |
registerDriver(); |
| 236 |
|
| 237 |
File f = new File(fName); |
| 238 |
boolean newdb = f != null && !f.exists(); |
| 239 |
|
| 240 |
SQLiteConfig config = new SQLiteConfig();
|
| 241 |
config.setSharedCache(true);
|
| 242 |
config.enableLoadExtension(true);
|
| 243 |
config.setTransactionMode(SQLiteConfig.TransactionMode.IMMEDIATE); |
| 244 |
config.setOpenMode(SQLiteOpenMode.NOMUTEX); |
| 245 |
|
| 246 |
// config.setJournalMode(SQLiteConfig.JournalMode.WAL);
|
| 247 |
// config.setTransactionMode(SQLiteConfig.TransactionMode.DEFERRED);
|
| 248 |
// config.setSynchronous(SQLiteConfig.SynchronousMode.OFF);
|
| 249 |
// config.setOpenMode(SQLiteOpenMode.FULLMUTEX);
|
| 250 |
config.setPragma(SQLiteConfig.Pragma.CASE_SENSITIVE_LIKE, "true");
|
| 251 |
|
| 252 |
SQLiteConnection conn; |
| 253 |
|
| 254 |
try {
|
| 255 |
conn = org.sqlite.JDBC.createConnection(getConnectionURL(f), config.toProperties()); |
| 256 |
} catch (Throwable th) { |
| 257 |
throw th;
|
| 258 |
} |
| 259 |
|
| 260 |
Functions.register_all(conn); |
| 261 |
|
| 262 |
if (newdb) {
|
| 263 |
String[] sqls2 = new String[]{ |
| 264 |
"CREATE TABLE IF NOT EXISTS \"" + TABLE_RESOURCES_NAME + "\"(\"" + FIELD_RESOURCES_NAME + "\" VARCHAR(150) NOT NULL, \"" + FIELD_RESOURCES_RESOURCE + "\" BLOB DEFAULT NULL , PRIMARY KEY(\"" + FIELD_RESOURCES_NAME + "\"))", |
| 265 |
"CREATE TABLE IF NOT EXISTS \"" + TABLE_CONFIGURATION_NAME + "\"(\"" + FIELD_CONFIGURATION_NAME + "\" VARCHAR(200) NOT NULL, \"" + FIELD_CONFIGURATION_VALUE + "\" CLOB DEFAULT NULL, PRIMARY KEY(\"" + FIELD_CONFIGURATION_NAME + "\"))" |
| 266 |
}; |
| 267 |
for (String sql : sqls2) { |
| 268 |
try {
|
| 269 |
conn.createStatement().execute(sql); |
| 270 |
} catch (SQLException ex) { |
| 271 |
LOGGER.debug("Can't configure gvsig tables.", ex);
|
| 272 |
LOGGER.warn("Can't configure gvsig tables. " + sql);
|
| 273 |
// Ignore this error.
|
| 274 |
} |
| 275 |
} |
| 276 |
} |
| 277 |
return conn;
|
| 278 |
} |
| 279 |
|
| 280 |
public static String getConnectionURL(File dbFile) { |
| 281 |
String fname = dbFile.getAbsolutePath().replace("\\", "/"); |
| 282 |
if (StringUtils.isBlank(FilenameUtils.getExtension(fname))) {
|
| 283 |
if (fname.endsWith(".")) { |
| 284 |
fname = fname + GeopackageUtils.EXTENSION; |
| 285 |
} else {
|
| 286 |
fname = fname + "." + GeopackageUtils.EXTENSION;
|
| 287 |
} |
| 288 |
} |
| 289 |
String connectionURL = "jdbc:sqlite:" + fname; |
| 290 |
return connectionURL;
|
| 291 |
} |
| 292 |
|
| 293 |
private static void registerDriver() throws SQLException { |
| 294 |
String className = "org.sqlite.JDBC"; |
| 295 |
if (className == null) { |
| 296 |
return;
|
| 297 |
} |
| 298 |
try {
|
| 299 |
Class theClass = Class.forName(className); |
| 300 |
if (theClass == null) { |
| 301 |
throw new JDBCDriverClassNotFoundException(SQLiteLibrary.NAME, className); |
| 302 |
} |
| 303 |
} catch (Exception e) { |
| 304 |
throw new SQLException("Can't register JDBC driver '" + className + "'.", e); |
| 305 |
} |
| 306 |
} |
| 307 |
|
| 308 |
} |