1 import SQLite.*; 2 import java.io.*; 3 4 public class test3 implements SQLite.Trace, SQLite.Profile { 5 trace(String stmt)6 public void trace(String stmt) { 7 System.out.println("TRACE: " + stmt); 8 } 9 profile(String stmt, long est)10 public void profile(String stmt, long est) { 11 System.out.println("PROFILE(" + est + "): " + stmt); 12 } 13 prep_ins(SQLite.Database db, String sql)14 SQLite.Stmt prep_ins(SQLite.Database db, String sql) 15 throws SQLite.Exception { 16 return db.prepare(sql); 17 } 18 do_ins(SQLite.Stmt stmt)19 void do_ins(SQLite.Stmt stmt) throws SQLite.Exception { 20 stmt.reset(); 21 while (stmt.step()) { 22 } 23 } 24 do_ins(SQLite.Stmt stmt, int i, double d, String t, byte[] b)25 void do_ins(SQLite.Stmt stmt, int i, double d, String t, byte[] b) 26 throws SQLite.Exception { 27 stmt.reset(); 28 stmt.bind(1, i); 29 stmt.bind(2, d); 30 stmt.bind(3, t); 31 stmt.bind(4, b); 32 while (stmt.step()) { 33 } 34 } 35 do_exec(SQLite.Database db, String sql)36 void do_exec(SQLite.Database db, String sql) throws SQLite.Exception { 37 SQLite.Stmt stmt = db.prepare(sql); 38 while (stmt.step()) { 39 } 40 stmt.close(); 41 } 42 do_select(SQLite.Database db, String sql)43 void do_select(SQLite.Database db, String sql) throws SQLite.Exception { 44 SQLite.Stmt stmt = db.prepare(sql); 45 int row = 0; 46 while (stmt.step()) { 47 int i, ncol = stmt.column_count(); 48 System.out.println("=== ROW " + row + "==="); 49 for (i = 0; i < ncol; i++) { 50 try { 51 System.out.print(stmt.column_database_name(i) + "." + 52 stmt.column_table_name(i) + "." + 53 stmt.column_origin_name(i) + "<" + 54 stmt.column_decltype(i) + ">="); 55 } catch (SQLite.Exception se) { 56 System.out.print("COLUMN#" + i + "<" + 57 stmt.column_decltype(i) + ">="); 58 } 59 Object obj = stmt.column(i); 60 if (obj == null) { 61 System.out.println("null<null>"); 62 } else if (obj instanceof byte[]) { 63 byte[] b = (byte[]) obj; 64 String sep = ""; 65 System.out.print("{"); 66 for (i = 0; i < b.length; i++) { 67 System.out.print(sep + b[i]); 68 sep = ","; 69 } 70 System.out.print("}"); 71 } else { 72 System.out.print(obj.toString()); 73 } 74 if (obj != null) { 75 System.out.println("<" + obj.getClass().getName() + ">"); 76 } 77 } 78 row++; 79 } 80 stmt.close(); 81 } 82 main(String args[])83 public static void main(String args[]) { 84 boolean error = true; 85 test3 T = new test3(); 86 System.out.println("LIB version: " + SQLite.Database.version()); 87 SQLite.Database db = new SQLite.Database(); 88 try { 89 byte[] b; 90 db.open("db3", 0666); 91 System.out.println("DB version: " + db.dbversion()); 92 db.busy_timeout(1000); 93 db.busy_handler(null); 94 db.trace(T); 95 db.profile(T); 96 T.do_exec(db, "create table TEST3("+ 97 "i integer, d double, t text, b blob)"); 98 T.do_exec(db, "create table B(" + 99 "id integer primary key, val blob)"); 100 T.do_select(db, "select * from sqlite_master"); 101 Stmt ins = T.prep_ins(db, "insert into TEST3(i,d,t,b)" + 102 " VALUES(:one,:two,:three,:four)"); 103 System.out.println("INFO: " + ins.bind_parameter_count() + 104 " parameters"); 105 for (int i = 1; i <= ins.bind_parameter_count(); i++) { 106 String name = ins.bind_parameter_name(i); 107 if (name != null) { 108 System.out.println("INFO: name for " + i + " is " + name); 109 System.out.println("INFO: index of " + name + " is " + 110 ins.bind_parameter_index(name)); 111 } 112 } 113 b = new byte[4]; 114 b[0] = 1; b[1] = 1; b[2] = 2; b[3] = 3; 115 T.do_ins(ins, 1, 2.4, "two point four", b); 116 T.do_ins(ins); 117 b[0] = -1; b[1] = -2; b[2] = -3; b[3] = -4; 118 T.do_ins(ins, 2, 4.8, "four point eight", b); 119 T.do_ins(ins); 120 T.do_ins(ins, 3, -3.333, null, null); 121 ins.close(); 122 T.do_select(db, "select * from TEST3"); 123 T.do_exec(db, "insert into B values(NULL, zeroblob(128))"); 124 T.do_exec(db, "insert into B values(NULL, zeroblob(128))"); 125 T.do_exec(db, "insert into B values(NULL, zeroblob(128))"); 126 T.do_select(db, "select id from B"); 127 byte[] b128 = new byte[128]; 128 for (int i = 0; i < b128.length; i++) { 129 b128[i] = (byte) i; 130 } 131 Blob blob = db.open_blob("main", "B", "val", 1, true); 132 OutputStream os = blob.getOutputStream(); 133 os.write(b128); 134 os.close(); 135 blob.close(); 136 blob = db.open_blob("main", "B", "val", 3, true); 137 os = blob.getOutputStream(); 138 os.write(b128); 139 os.close(); 140 blob.close(); 141 T.do_select(db, "select * from B"); 142 blob = db.open_blob("main", "B", "val", 1, false); 143 InputStream is = blob.getInputStream(); 144 is.skip(96); 145 is.read(b); 146 is.close(); 147 blob.close(); 148 System.out.println("INFO: expecting {96,97,98,99} got {" + 149 b[0] + "," + b[1] + "," + 150 b[2] + "," + b[3] + "}"); 151 System.out.println("INFO: backup begin"); 152 try { 153 SQLite.Database dbdest = new SQLite.Database(); 154 dbdest.open("db3-backup", 0666); 155 dbdest.busy_timeout(1000); 156 dbdest.busy_handler(null); 157 Backup backup = db.backup(dbdest, "main", "main"); 158 while (!backup.step(1)) { 159 System.out.println("INFO: backup step: " 160 + backup.remaining() + "/" 161 + backup.pagecount()); 162 } 163 b = null; 164 System.out.println("INFO: backup end"); 165 } catch (java.lang.Exception ee) { 166 System.err.println("error: " + ee); 167 } 168 int info[] = new int[2]; 169 SQLite.Database.status(SQLite.Constants.SQLITE_STATUS_MALLOC_COUNT, info, false); 170 System.out.println("INFO: status(STATUS_MALLOC_COUNT) = " 171 + info[0] + "/" + info[1]); 172 SQLite.Database.status(SQLite.Constants.SQLITE_STATUS_MALLOC_SIZE, info, false); 173 System.out.println("INFO: status(STATUS_MALLOC_SIZE) = " 174 + info[0] + "/" + info[1]); 175 SQLite.Database.status(SQLite.Constants.SQLITE_STATUS_MEMORY_USED, info, false); 176 System.out.println("INFO: status(STATUS_MEMORY_USED) = " 177 + info[0] + "/" + info[1]); 178 SQLite.Database.status(SQLite.Constants.SQLITE_STATUS_PAGECACHE_OVERFLOW, info, false); 179 System.out.println("INFO: status(STATUS_PAGECACHE_OVERFLOW) = " 180 + info[0] + "/" + info[1]); 181 SQLite.Database.status(SQLite.Constants.SQLITE_STATUS_PAGECACHE_SIZE, info, false); 182 System.out.println("INFO: status(STATUS_PAGECACHE_SIZE) = " 183 + info[0] + "/" + info[1]); 184 SQLite.Database.status(SQLite.Constants.SQLITE_STATUS_PAGECACHE_USED, info, false); 185 System.out.println("INFO: status(STATUS_PAGECACHE_USED) = " 186 + info[0] + "/" + info[1]); 187 SQLite.Database.status(SQLite.Constants.SQLITE_STATUS_PARSER_STACK, info, false); 188 System.out.println("INFO: status(STATUS_PARSER_STACK) = " 189 + info[0] + "/" + info[1]); 190 SQLite.Database.status(SQLite.Constants.SQLITE_STATUS_SCRATCH_OVERFLOW, info, false); 191 System.out.println("INFO: status(STATUS_SCRATCH_OVERFLOW) = " 192 + info[0] + "/" + info[1]); 193 SQLite.Database.status(SQLite.Constants.SQLITE_STATUS_SCRATCH_SIZE, info, false); 194 System.out.println("INFO: status(STATUS_SCRATCH_SIZE) = " 195 + info[0] + "/" + info[1]); 196 SQLite.Database.status(SQLite.Constants.SQLITE_STATUS_SCRATCH_USED, info, false); 197 System.out.println("INFO: status(STATUS_SCRATCH_USED) = " 198 + info[0] + "/" + info[1]); 199 db.db_status(SQLite.Constants.SQLITE_DBSTATUS_CACHE_HIT, info, false); 200 System.out.println("INFO: db_status(DBSTATUS_CACHE_HIT) = " 201 + info[0] + "/" + info[1]); 202 db.db_status(SQLite.Constants.SQLITE_DBSTATUS_CACHE_MISS, info, false); 203 System.out.println("INFO: db_status(DBSTATUS_CACHE_MISS) = " 204 + info[0] + "/" + info[1]); 205 db.db_status(SQLite.Constants.SQLITE_DBSTATUS_CACHE_USED, info, false); 206 System.out.println("INFO: db_status(DBSTATUS_CACHE_USED) = " 207 + info[0] + "/" + info[1]); 208 db.db_status(SQLite.Constants.SQLITE_DBSTATUS_LOOKASIDE_HIT, info, false); 209 System.out.println("INFO: db_status(DBSTATUS_LOOKASIZE_HIT) = " 210 + info[0] + "/" + info[1]); 211 db.db_status(SQLite.Constants.SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, info, false); 212 System.out.println("INFO: db_status(DBSTATUS_LOOKASIZE_MISS_FULL) = " 213 + info[0] + "/" + info[1]); 214 db.db_status(SQLite.Constants.SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, info, false); 215 System.out.println("INFO: db_status(DBSTATUS_LOOKASIZE_MISS_SIZE) = " 216 + info[0] + "/" + info[1]); 217 db.db_status(SQLite.Constants.SQLITE_DBSTATUS_LOOKASIDE_USED, info, false); 218 System.out.println("INFO: db_status(DBSTATUS_LOOKASIZE_USED) = " 219 + info[0] + "/" + info[1]); 220 db.db_status(SQLite.Constants.SQLITE_DBSTATUS_SCHEMA_USED, info, false); 221 System.out.println("INFO: db_status(DBSTATUS_SCHEMA_USED) = " 222 + info[0] + "/" + info[1]); 223 db.db_status(SQLite.Constants.SQLITE_DBSTATUS_STMT_USED, info, false); 224 System.out.println("INFO: db_status(DBSTATUS_STMT_USED) = " 225 + info[0] + "/" + info[1]); 226 T.do_exec(db, "drop table TEST3"); 227 T.do_exec(db, "drop table B"); 228 T.do_select(db, "select * from sqlite_master"); 229 error = false; 230 } catch (java.lang.Exception e) { 231 System.err.println("error: " + e); 232 e.printStackTrace(); 233 } finally { 234 try { 235 System.err.println("cleaning up ..."); 236 try { 237 T.do_exec(db, "drop table TEST3"); 238 T.do_exec(db, "drop table B"); 239 } catch(SQLite.Exception e) { 240 } 241 db.close(); 242 } catch(java.lang.Exception e) { 243 System.err.println("error: " + e); 244 error = true; 245 } finally { 246 System.err.println("done."); 247 } 248 } 249 if (error) { 250 System.exit(1); 251 } 252 } 253 } 254