[sqlite] Date as integer
On 12/29/15, Cecil Westerhof wrote:
> I first had the following table:
> CREATE TABLE simpleLog (
>datetimeTEXT NOT NULL PRIMARY KEY DEFAULT CURRENT_TIMESTAMP,
>description TEXT NOT NULL
> )
>
> ?But datetime then takes 19 bytes. I understood you can also use an Integer
> or Real and that this should be more efficient. At the moment I have the
> following (I do not expect more as one record in a second):
> CREATE TABLE simpleLog (
>datetimeINT NOT NULL PRIMARY KEY DEFAULT (strftime('%s')),
>description TEXT NOT NULL
> )
>
> And a select is then done by (in my select minute is precision enough):
> SELECT strftime('%Y-%m-%d %H:%M', datetime, 'unixepoch', 'localtime') as
> datetime
> ,description
> FROM simpleLog
> ORDER BY datetime DESC
>
> Is this a good way to go, or is there a better way?
What you have should work well.
If you store the date/times as a floating-point Julian Day Number, you
can omit the 'unixepoch' on query. Use julianday('now') instead of
strftime('%s','now') on the DEFAULT. That seems a little simpler to
me, and you get millisecond resolution on the date/times instead of
just second resolution. But the unix-time format is more familar to
many programmers, and can be stored in 4 bytes instead of 8.
--
D. Richard Hipp
drh at sqlite.org
[sqlite] whish list for 2016
On Dec 24, 2015, at 7:49 PM, Simon Slavin wrote: > > What makes "ALTER TABLE table-name DROP ?COLUMN" hard is checking the schema > to make sure that nothing in the schema refers to the dropped column. Given that the current alternative to this feature is hand-rolled code like I gave earlier in the thread, I don?t see that SQLite *must* solve this problem. It would certainly be nice if it maintained consistency for you, but since the alternative doesn?t allow you to do that, what?s wrong with just putting a warning in the docs: ?If you use ALTER TABLE DROP COLUMN, you risk breaking consistency checks.? Then you can push off automatic consistency check maintenance to the quasi-mythical SQLite 4. Again, all I?m advocating for is automatic generation and running of the SQL I gave above. That is, implement the feature in terms of existing facilities, don?t go creating a bunch of new code just to handle this case. Not only does this make implementation easier, it will reduce the temptation to make the feature conditional to keep the embedded users happy, which in turn makes testing harder, since it doubles the number of test cases.
[sqlite] Using colation in Java
On 29 December 2015 at 08:23, Cecil Westerhof
wrote:
> When working in Python I can use:
> con.create_collation("mycollation", collate)
>
> To change the sort order. How should I do this in Java?
>
Note there are multiple ways to use sqlite from java, so it would help to
specify which bindings you are using. eg. sqlite4java doesn't provide a
create_collation call as far as I can tell.
Based on your previous posts I assume you are accessing sqlite via JDBC; I
can be no help there.
-Rowan
[sqlite] Using colation in Java
2015-12-29 1:35 GMT+01:00 Rowan Worth :
> On 29 December 2015 at 08:23, Cecil Westerhof
> wrote:
>
> > When working in Python I can use:
> > con.create_collation("mycollation", collate)
> >
> > To change the sort order. How should I do this in Java?
> >
>
> Note there are multiple ways to use sqlite from java, so it would help to
> specify which bindings you are using. eg. sqlite4java doesn't provide a
> create_collation call as far as I can tell.
>
> Based on your previous posts I assume you are accessing sqlite via JDBC; I
> can be no help there.
>
?I am using:
https://github.com/xerial/sqlite-jdbc
For the moment being I read everything in an ArrayList and I sort that with
a Collation. Not the best solution, but the output is at the moment only 51
KB, so not a problem now.
Instead of using (as I should):
bw= new BufferedWriter(new FileWriter(outputfile));
stmt = conn.createStatement();
rs= stmt.executeQuery(selectProverbs);
while (rs.next()) {
bw.write(rs.getString("proverb") + "\n");
}
rs.close();
stmt.close();
bw.close();
I am using at the moment:
stmt = conn.createStatement();
rs= stmt.executeQuery(selectProverbs);
while (rs.next()) {
lineList.add(rs.getString("proverb"));
}
rs.close();
stmt.close();
Collections.sort(lineList, collator);
out = new PrintWriter(new FileWriter(outputfile));
for (String outputLine : lineList) {
out.println(outputLine);
}
out.flush();
out.close();
--
Cecil Westerhof
[sqlite] Using colation in Java
When working in Python I can use:
con.create_collation("mycollation", collate)
To change the sort order. How should I do this in Java?
--
Cecil Westerhof
[sqlite] SQLITE_DEFAULT_WORKER_THREADS & SQLITE_MAX_WORKER_THREADS
Can you help me understand the intent in these lines? Referring to sqlite3.c amalgamation version 3.9.2 for Windows, lines 9531 to 9540: #ifndef SQLITE_MAX_WORKER_THREADS # define SQLITE_MAX_WORKER_THREADS 8 #endif #ifndef SQLITE_DEFAULT_WORKER_THREADS # define SQLITE_DEFAULT_WORKER_THREADS 0 #endif #if SQLITE_DEFAULT_WORKER_THREADS>SQLITE_MAX_WORKER_THREADS # undef SQLITE_MAX_WORKER_THREADS # define SQLITE_MAX_WORKER_THREADS SQLITE_DEFAULT_WORKER_THREADS #endif If SQLITE_MAX_WORKER_THREADS is not predefined, it defaults to 8. If SQLITE_DEFAULT_WORKER_THREADS is predefined let's say to 12, then SQLITE_MAX_WORKER_THREADS is raised to match the default. I must be missing something important here (the code is large and I'm only hitting its very surface for now), but wasn't the intent to limit the DEFAULT within the MAX interval? That is: #if SQLITE_DEFAULT_WORKER_THREADS>SQLITE_MAX_WORKER_THREADS # undef SQLITE_DEFAULT_WORKER_THREADS # define SQLITE_DEFAULT_WORKER_THREADS SQLITE_MAX_WORKER_THREADS #endif Thank you very much, -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
[sqlite] SQLITE_DEFAULT_WORKER_THREADS & SQLITE_MAX_WORKER_THREADS
On 28 Dec 2015, at 11:24pm, Olivier Mascia wrote: > If SQLITE_MAX_WORKER_THREADS is not predefined, it defaults to 8. > If SQLITE_DEFAULT_WORKER_THREADS is predefined let's say to 12, then > SQLITE_MAX_WORKER_THREADS is raised to match the default. > > I must be missing something important here (the code is large and I'm only > hitting its very surface for now), but wasn't the intent to limit the DEFAULT > within the MAX interval? The logic is the other way around. The code you posted allows the programmer to set a value for SQLITE_DEFAULT_WORKER_THREADS and know that SQLite will allow the default they set. Without that complicated code the compiler would allow them to set a DEFAULT and SQLite might never be able to reach the default they set. Simon.
