Re: [sqlite] sqlite bug after CREATE UNIQUE INDEX
I think the point is that attempting to create the unique index should fail (with an error) and the index not be created rather than creating a unique index with duplicates (or whatever it is doing) causing subsequent queries to return incorrect results. >On Wed, 30 Jul 2014 09:10:29 +0400 >Pavel Pimenov wrote: > >> CREATE TABLE fly_hash_block(tth_id integer PRIMARY KEY NOT NULL, tth >> number NOT NULL); >> INSERT INTO fly_hash_block VALUES(1,1); >> INSERT INTO fly_hash_block VALUES(2,2); >> INSERT INTO fly_hash_block VALUES(3,2); >> *CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth);* > >Not an answer if the table already exists, but a better solution would >be to constrain the table: > >sqlite> CREATE TABLE fly_hash_block( > tth_id integer PRIMARY KEY NOT NULL, > tth number not null UNIQUE); >sqlite> INSERT INTO fly_hash_block VALUES(1,1); >sqlite> INSERT INTO fly_hash_block VALUES(2,2); >sqlite> INSERT INTO fly_hash_block VALUES(3,2); >Error: column tth is not unique >sqlite> > >--jkl >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Counting changes in a INSERT/REPLACE
On Tue, 29 Jul 2014 16:49:04 -0400 "jose isaias cabrera" wrote: > BEGIN; > INSERT OR REPLACE INTO A > SELECT * FROM client.A WHERE id = 1 AND Date != '2014-06-22'; > INSERT OR REPLACE INTO A > SELECT * FROM client.A WHERE id = 2 AND Date != '2014-06-22'; > ... > ... > INSERT OR REPLACE INTO A > SELECT * FROM client.A WHERE id = 19 AND Date != > '2014-06-22'; INSERT OR REPLACE INTO A > SELECT * FROM client.A WHERE id = 20 AND Date != > '2014-06-22'; > END; Wouldn't the following be simpler and faster? INSERT OR REPLACE INTO A SELECT * FROM client.A WHERE id between 1 and 20 AND Date != '2014-06-22'; --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bug after CREATE UNIQUE INDEX
On Wed, 30 Jul 2014 09:10:29 +0400 Pavel Pimenov wrote: > CREATE TABLE fly_hash_block(tth_id integer PRIMARY KEY NOT NULL, tth > number NOT NULL); > INSERT INTO fly_hash_block VALUES(1,1); > INSERT INTO fly_hash_block VALUES(2,2); > INSERT INTO fly_hash_block VALUES(3,2); > *CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth);* Not an answer if the table already exists, but a better solution would be to constrain the table: sqlite> CREATE TABLE fly_hash_block( tth_id integer PRIMARY KEY NOT NULL, tth number not null UNIQUE); sqlite> INSERT INTO fly_hash_block VALUES(1,1); sqlite> INSERT INTO fly_hash_block VALUES(2,2); sqlite> INSERT INTO fly_hash_block VALUES(3,2); Error: column tth is not unique sqlite> --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling Timezones
On 30/07/14 10:51, Nico Williams wrote: > I find that somewhat obnoxious. I often prefer absolute time It depends on the content being shown. We go for human friendly relative times (eg "13 hours ago") and then have a tooltip that gives the full timestamp. Doing maths on times and dates is annoyingly hard, which the relative value caters for. The developer/product manager can work out what is most appropriate for their users - eg showing only relative times, full with tooltip as relative, both relative and full etc. But yeah, this is formatting. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling Timezones
On Wed, Jul 30, 2014 at 12:34 PM, Roger Binns wrote: > On 30/07/14 10:05, Nico Williams wrote: >> Users travel; they don't have a single timezone. What matters is: the >> TZ when a user posted / did something, so you can have a vague idea of >> when they might be sleeping / unavailable. > > I'm not sure if you are disagreeing or agreeing with me. I wasn't either. But I am now: I'm agreeing with you: timezone needs to be accounted for as close to the user as possible. In a web application, as Simon points out, this should be done in JavaScript on the page, not on the server. > There is no need for timezones even for your example. Displaying the > timestamp relatively solves that (eg "13 hours ago"). I find that somewhat obnoxious. I often prefer absolute time and, and with a clue as to the poster's timezone (e.g., as in e-mail), as that tells me a lot about the user. On the other hand, why should I know that about the user? It's an information leak they might not like. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling Timezones
On 30/07/14 10:05, Nico Williams wrote: > Users travel; they don't have a single timezone. What matters is: the > TZ when a user posted / did something, so you can have a vague idea of > when they might be sleeping / unavailable. I'm not sure if you are disagreeing or agreeing with me. A clearer way of saying it is that I believe timezones are a formatting issue, and best dealt as close to the user as possible. For this specific discussion the user is in front of a browser, so I believe the browser is the best place to care about the timezones. Browsers pick up timezone and formatting preferences from the operating system they run on which in turn is based on user preferences. There is no need for timezones even for your example. Displaying the timestamp relatively solves that (eg "13 hours ago"). Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling Timezones
On 30 Jul 2014, at 6:05pm, Nico Williams wrote: > Ideally we'd all just use Zulu time all the time, but that won't fly. If this is web-facing, the problem is solved. JavaScript can be told to return 'now' expressed in UTC. "The Date.now() method returns the number of milliseconds elapsed since 1 January 1970 00:00:00 UTC." If you assume that every user has the timezone set correctly on their computer, you just need to trust what JavaScript returns. A timezone selected by the user is used only when displaying dates, or in setting dates in the future. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling Timezones
On Wed, Jul 30, 2014 at 11:53 AM, Roger Binns wrote: > Why do you even need to store their timezone? The only time it would matter > is if you are showing one user what another users local time is. Users travel; they don't have a single timezone. What matters is: the TZ when a user posted / did something, so you can have a vague idea of when they might be sleeping / unavailable. In practice asking a user for their timezone every time they login is obnoxious. And they might travel without logging out. Ideally we'd all just use Zulu time all the time, but that won't fly. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling Timezones
On 29/07/14 17:23, Will Fong wrote: > Ah! I have not explained my issue properly :) I'm very sorry about that. > > I'm using SQLite as a backend to a small website and I have users in > multiple timezones. When users login, their timezone is retrieved from > the user table. Why do you even need to store their timezone? The only time it would matter is if you are showing one user what another users local time is. The way I do this with web stuff is let the browser handle it. The page is rendered at the server with some reasonable default (UTC), and then javascript in the browser updates it for the local timezone and preferences. eg: 2014-07-30 16:43 UTC The Javascript then replaces the text with "7/30/2014 9:43" and leaves the tooltip as the UTC time. We usually make the text more useful - for example it may say "30 minutes ago", "Tuesday at 2pm", "3 years ago" etc and automatically update as the page is left open. This is the library we use, but there are many out there: https://mattbradley.github.io/livestampjs/ This approach means there is no need to store timezones, and that the formatting (eg 24 hr versus am/pm, timezone) are picked up from their browser and OS preferences. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What average is avg()?
On 30 Jul 2014, at 8:51am, Clemens Ladisch wrote: > Jonathan Moules wrote: >> Which type of average does avg() calculate? >> >> I guess it's the mean, but it could be median or mode, so worth asking. > > The SQL standard says it's the mean. Might be worth noting that avg() treats null values the same way that sum() treats nulls: it ignores them and does not include them in the divisor, unless all the values are null, in which case it returns null. This may not be what you are expecting. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling Timezones
On Tuesday, 29 July, 2014 20:31 Will Fong said:
>On Wed, Jul 30, 2014 at 10:01 AM, Keith Medcalf
>wrote:
>> Store and retrieve everything in the database in Zulu time. Whether
>this means using timestrings, UNIX timestamps, JD or MJD floats is up to
>you. The application (user interface) is responsible for converting
>retrieved data to the "display timezone" on output and convert data from
>the "input timezone" on input.
>> This is the only reliable way to handle multiple timezones. There are
>lots of moronic ways and five-nines (asctually more like nine-nines) of
>all software written use those moronic methods and for that reason do not
>work properly ("not work properly" being defined as anything somewhere
>between producing incorrect or ludicrously entertaining results and just
>puking all over the floor). In many cases "not working" but "not puking"
>is acceptable provided that the moronic behaviour is internally
>consistent. In others, "not working" is fatal.
>Well, PostgreSQL's method for this to be handled at the
>client/connection level seemed to have worked very well, and doesn't
>seem very "moronic". Having this handled at the database level makes
>the application(s) much less prone to bugs.
PostgreSQL's methods seem well designed (according to the current
documentation). It uses a reasonable timezone database (Olsen), allows you to
specify the "presentation" timezone (the timezone from which timestamps are
converted on input and to which timestamps are converted on output), stores
only GMT/UTC, and performs all conversions on "initial" input/"final" output,
while working entirely in GMT/UTC internally.
In other words, it does exactly what I said needs to be done at the
presentation layer within the SQL input/output parser routines.
Many products are far more brain-dead.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite bug after CREATE UNIQUE INDEX
hi
C:\!dc-db\db-sqlite-corrupt\db-uniq-bug>sqlite3 --version
3.8.5 2014-06-04 14:06:34 b1ed4f2a34ba66c29b130f8d13e9092758019212
C:\!dc-db\db-sqlite-corrupt\db-uniq-bug>sqlite3.exe FlylinkDC.sqlite
0
SQL> CREATE TABLE fly_hash_block(tth_id integer PRIMARY KEY NOT NULL, tth
number NOT NULL);
Table created
SQL> INSERT INTO fly_hash_block VALUES(1,1);
1 row inserted
SQL> INSERT INTO fly_hash_block VALUES(2,2);
1 row inserted
SQL> INSERT INTO fly_hash_block VALUES(3,2);
1 row inserted
SQL> CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth);
CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth)
*ORA-01452: CREATE UNIQUE INDEX невозможно; найдены дублирующиеся ключи*
SQL> select * from fly_hash_block;
TTH_IDTTH
--- --
1 1
2 2
3 2
SQL> select distinct tth from fly_hash_block;
TTH
--
1
* 2*
SQL> select tth,count(*) from fly_hash_block group by tth;
TTH COUNT(*)
-- --
1 1
2 2
--
~PPA() {} //
http://flylinkdc.blogspot.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What average is avg()?
The convention, as far as I am aware, is "Average" always refers to the "Mean Average" unless explicitly stated otherwise. Modes and Medians are usually specific to certain arms of the calculati. Having said that, probably a good idea to add the note to the docs regardless. On 2014/07/30 09:38, Jonathan Moules wrote: Hi List, A question and possible suggestion. Which type of average does avg() calculate? The documentation doesn't say - https://www.sqlite.org/lang_aggfunc.html I guess it's the mean, but it could be median or mode, so worth asking. My suggestion would be to include an explicit statement in the docs saying which it is to make it clearer for users. Cheers, Jonathan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What average is avg()?
Jonathan Moules wrote: > Which type of average does avg() calculate? > > I guess it's the mean, but it could be median or mode, so worth asking. The SQL standard says it's the mean. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What average is avg()?
Hi List, A question and possible suggestion. Which type of average does avg() calculate? The documentation doesn't say - https://www.sqlite.org/lang_aggfunc.html I guess it's the mean, but it could be median or mode, so worth asking. My suggestion would be to include an explicit statement in the docs saying which it is to make it clearer for users. Cheers, Jonathan -- This transmission is intended for the named addressee(s) only and may contain confidential, sensitive or personal information and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All email traffic sent to or from us, including without limitation all GCSX traffic, may be subject to recording and/or monitoring in accordance with relevant legislation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable values in Views
Hi List, Thanks for the responses. I don't think TCL will work for me - I want to use less languages, not more. As to the structure - I am considering using ATTACH as a method, but haven't gotten to the point where I need to decide which of the three options (keys in tables, table sets, or separate ATTACHed databases) to use. I think personally I prefer the logical separation of the ATTACH method but haven't fully looked into it yet. Cheers, Jonathan On 30 July 2014 07:00, Noel Frankinet wrote: > There a tcl binding to sqlite, maybe it could help you ? > > Noël > > > On 30 July 2014 08:44, Sylvain Pointeau > wrote: > > > It is called parameterized view in sqlserver. > > Actually it is extremely useful in order to have a good reusability in > the > > code. > > I was actually missing it in Oracle, although I found a workaround of > using > > the pipelined functions. > > > > Unfortunately, it is missing in sqlite, as well as the merge statement, > > also very useful (insert or replace has just to be avoided) > > > > What I used to make is to use a shell script, and to use sed to replace > my > > variable before executing the script... Far from ideal but it worked ok, > it > > is just annoying because we have to prepare the file first. > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > Noël Frankinet > Strategis sprl > 0478/90.92.54 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- This transmission is intended for the named addressee(s) only and may contain confidential, sensitive or personal information and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All email traffic sent to or from us, including without limitation all GCSX traffic, may be subject to recording and/or monitoring in accordance with relevant legislation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable values in Views
There a tcl binding to sqlite, maybe it could help you ? Noël On 30 July 2014 08:44, Sylvain Pointeau wrote: > It is called parameterized view in sqlserver. > Actually it is extremely useful in order to have a good reusability in the > code. > I was actually missing it in Oracle, although I found a workaround of using > the pipelined functions. > > Unfortunately, it is missing in sqlite, as well as the merge statement, > also very useful (insert or replace has just to be avoided) > > What I used to make is to use a shell script, and to use sed to replace my > variable before executing the script... Far from ideal but it worked ok, it > is just annoying because we have to prepare the file first. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Noël Frankinet Strategis sprl 0478/90.92.54 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
