[sqlite] Problem with accumulating decimal values
Hello !then I did a modification to sqlite3 that basically define a new type "sqlite_double" and use it instead of "double" (#define sqlite_double? double) then I can redefine it to _Decimal64 (#define sqlite_double? _Decimal64) this way with a modern C compiler we can have sqlite3 using decimal arithmetic. There is some more macros not mentioned here (see at https://github.com/mingodad/squilu/tree/master/SquiLu-ext), I just compiled it with gcc 4.9.3 and executed the examples from this original thread and it works fine. I'll say again that I still think that is a good idea to replace "double" by "sqlite_double" to allow alternative decimal/floating point implementations (on my case _Decimal64). Cheers ! ?
[sqlite] Problem with accumulating decimal values
From: Frank Millman Sent: Friday, December 11, 2015 4:21 PM To: sqlite-users at mailinglists.sqlite.org Subject: [sqlite] Problem with accumulating decimal values > Hi all > > I am having a problem accumulating decimal values. > > I am actually using Python, but I can reproduce it in the sqlite3 interactive > terminal. > Many thanks for all the replies. I understand what is happening now. I have found an effective workaround. The Python sqlite3 module allows you to create a user-defined function that you can use from within SQL statements. I wrote a function that uses the Python Decimal module to perform the arithmetic and return the result, and it seems to work just fine. Thanks again. Frank
[sqlite] Problem with accumulating decimal values
Frank, The problem you are having is due to SQLite not following the SQL standard regarding non-integral numeric types. The SQL standard specifies that the DECIMAL type is exact numeric and able to represent decimal numbers exactly. However, when you ask SQLite for a DECIMAL column, that is not what it will give you; instead, it will silently "succeed" but give you an inexact numeric type instead, a floating point number, as if you had said FLOAT/etc instead of DECIMAL. So the problem you are having is due to the actual numbers in the database not being what you told it to store, but just an approximation. Per another suggestion, the best workaround is to use an INTEGER type instead, and store an even multiple of whatever your smallest currency unit size is, eg cents rather than dollars. -- Darren Duncan On 2015-12-11 6:21 AM, Frank Millman wrote: > I am having a problem accumulating decimal values. > > I am actually using Python, but I can reproduce it in the sqlite3 interactive > terminal. > > SQLite version 3.8.6 2014-08-15 11:46:33 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL); > sqlite> INSERT INTO fmtemp VALUES (1, 0); > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 123.45
[sqlite] Remarks about vtab generate_series.
Hello, I have two mini minor remarks about the series.c example as of 2015-08-21 and referred to from the Table-Valued Functions paragraph of https://www.sqlite .org/vtab.html#tabfunc2 1. comment above seriesDisconnect < ** This method is the destructor for series_cursor objects. > ** This method is the destructor for series_vtab objects. 2. estimatedCost in seriesBestIndex A plan where all 3 parameters are passed in is now assigned the same cost as a plan where just the first 2 parameters are set. I can construct a case where the wrong plan is choosen, although it looks exaggerated: select value from t join generate_series(1,10,t.step) Possibly my remarks can be considered when the example is maintained for a future release. Thanks a lot. E. Pasma. ~ ~ ~
[sqlite] Virtual tables and table-valued functions
6 dec 2015, Charles Leifer: > In working on a Python wrapper around virtual tables, I thought it > might be > beneficial if SQLite provided an official C API for creating simple > table-valued functions. The wrapper could build on the existing > virtual > table APIs and would consist of: > > * user supplied list of parameters, which would be converted to HIDDEN > columns in a virtual table. > * user supplied list of column names, which would constitute the > return > values of the table-valued function > * user supplied initialization function which would accept the > parameters > passed, i.e. SELECT * FROM user_func(p1, p2, p3), the init function > would > receive (p1, p2, p3). > * user supplied iterate function which would accept a zero-based > index and > a result context, returning 1 to indicate EOF and 0 to indicate more > results exist. > > I think this might lead to a larger ecosystem of quality open-source > table-valued functions. Given the fact that creating even a simple > vtable > is cumbersome, providing an API that required the bare minimum seems > like a > good idea to me. > > Thoughts? > > PS if you're curious about the Python wrapper, I posted on it a day > or two > ago. One thought that is only a change in the current class, is to have a default xBestIndex method. This would just optimize for plans with the highest number of usable parameter values, starting from number 1.. The function may then become optional. In xFIlter the number of parameters is still available from the argc argument. The xBestIndex is rather verbose and it looks that in most cases it should do just the above.
[sqlite] Selecting total and not used in one query
?I have the following query: SELECT (SELECT COUNT(*) FROM proverbs)AS Total , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used But I want something like: ?SELECT (SELECT COUNT(*) FROM proverbs)AS Total , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used , (Total - Used) AS Free ?? ?But then I get: ?no such column: Total It can be done, because I have it done in the past, but obviously a little different. :'-( ?Anybody knows how it can be done?? -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
On 2015/12/12 4:18 PM, Cecil Westerhof wrote: > ?I have the following query: > SELECT > (SELECT COUNT(*) FROM proverbs)AS Total > , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used > > But I want something like: > ?SELECT > (SELECT COUNT(*) FROM proverbs)AS Total > , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used > , (Total - Used) AS Free SELECT (SELECT COUNT(*) FROM proverbs) AS Total , (SELECT COUNT(*) FROM proverbs WHERE used IS NOT NULL) AS Used , (SELECT COUNT(*) FROM proverbs WHERE used IS NULL) AS Free
[sqlite] Problem with accumulating decimal values
On Sat, Dec 12, 2015 at 6:51 AM, Darren Duncan wrote: > > Per another suggestion, the best workaround is to use an INTEGER type > instead, and store an even multiple of whatever your smallest currency unit > size is, eg cents rather than dollars. > As I understood, he is doing the math in Python and saving strings (that are produced by Python decimal arbitrary precision classes) to the database, what should work perfectly.
[sqlite] Selecting total and not used in one query
or, more elegantly... WITH PC(t, u, f) AS ( SELECT 1, (P.used IS NOT NULL), (P.used IS NULL) FROM proverbs AS P ) SELECT SUM(PC.t) AS Total, SUM(PC.u) AS Used, SUM(PC.f) AS Free FROM PC; On 2015/12/12 4:18 PM, Cecil Westerhof wrote: > ?I have the following query: > SELECT > (SELECT COUNT(*) FROM proverbs)AS Total > , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used > > But I want something like: > ?SELECT > (SELECT COUNT(*) FROM proverbs)AS Total > , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used > , (Total - Used) AS Free > ?? > ?But then I get: > ?no such column: Total > > It can be done, because I have it done in the past, but obviously a little > different. :'-( > > ?Anybody knows how it can be done?? >
[sqlite] Selecting total and not used in one query
2015-12-12 15:24 GMT+01:00 R Smith : > > > On 2015/12/12 4:18 PM, Cecil Westerhof wrote: > >> ?I have the following query: >> SELECT >> (SELECT COUNT(*) FROM proverbs)AS Total >> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >> >> But I want something like: >> ?SELECT >> (SELECT COUNT(*) FROM proverbs)AS Total >> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >> , (Total - Used) AS Free >> > > SELECT > (SELECT COUNT(*) FROM proverbs) AS Total > , (SELECT COUNT(*) FROM proverbs WHERE used IS NOT NULL) AS Used > , (SELECT COUNT(*) FROM proverbs WHERE used IS NULL) AS Free > ?That is what I am using now, but it is inefficient. This uses three scans instead of two.? -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
On 12-12-15 15:18, Cecil Westerhof wrote: > ?I have the following query: > SELECT > (SELECT COUNT(*) FROM proverbs)AS Total > , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used > > But I want something like: > ?SELECT > (SELECT COUNT(*) FROM proverbs)AS Total > , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used > , (Total - Used) AS Free > ?? > ?But then I get: > ?no such column: Total > > It can be done, because I have it done in the past, but obviously a little > different. :'-( > > ?Anybody knows how it can be done?? > SELECT Total, Used, Total-Used as Free FROM (SELECT (SELECT COUNT(*) FROM proverbs) AS Total , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used)
[sqlite] Selecting total and not used in one query
On 12-12-15 15:45, Luuk wrote: > > > On 12-12-15 15:18, Cecil Westerhof wrote: >> ?I have the following query: >> SELECT >> (SELECT COUNT(*) FROM proverbs) AS Total >> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >> >> But I want something like: >> ?SELECT >> (SELECT COUNT(*) FROM proverbs) AS Total >> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >> , (Total - Used) AS Free >> ?? >> ?But then I get: >> ?no such column: Total >> >> It can be done, because I have it done in the past, but obviously a >> little >> different. :'-( >> >> ?Anybody knows how it can be done?? >> > > > SELECT Total, Used, Total-Used as Free > FROM (SELECT (SELECT COUNT(*) > FROM proverbs) AS Total , > (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used) or: select Total, Used, Total-Used as Free from (select count(*) as Total, count(used) as Used from proverbs)
[sqlite] Selecting total and not used in one query
On 12/12/2015 9:18 AM, Cecil Westerhof wrote: > But I want something like: > ?SELECT > (SELECT COUNT(*) FROM proverbs)AS Total > , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used > , (Total - Used) AS Free select count(*) Total, sum(used is not null) Used, sum(used is null) Free, sum(any_boolean_condition) CountSatisfyingCondition from proverbs; -- Igor Tandetnik
[sqlite] Selecting total and not used in one query
2015-12-12 16:06 GMT+01:00 Luuk : > On 12-12-15 15:18, Cecil Westerhof wrote: > >> ?I have the following query: >>> SELECT >>> (SELECT COUNT(*) FROM proverbs) AS Total >>> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >>> >>> But I want something like: >>> ?SELECT >>> (SELECT COUNT(*) FROM proverbs) AS Total >>> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >>> , (Total - Used) AS Free >>> ?? >>> ?But then I get: >>> ?no such column: Total >>> >>> It can be done, because I have it done in the past, but obviously a >>> little >>> different. :'-( >>> >>> ?Anybody knows how it can be done?? >>> >>> >> >> SELECT Total, Used, Total-Used as Free >> FROM (SELECT (SELECT COUNT(*) >> FROM proverbs) AS Total , >> (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used) >> > > or: > select Total, Used, Total-Used as Free from (select count(*) as Total, > count(used) as Used from proverbs) > ?That is what I needed. I prefer the second one. Is probably a little less efficient, but much clearer.? -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
2015-12-12 16:23 GMT+01:00 Igor Tandetnik : > On 12/12/2015 9:18 AM, Cecil Westerhof wrote: > >> But I want something like: >> ?SELECT >> (SELECT COUNT(*) FROM proverbs)AS Total >> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >> , (Total - Used) AS Free >> > > select count(*) Total, >sum(used is not null) Used, >sum(used is null) Free, >sum(any_boolean_condition) CountSatisfyingCondition > from proverbs; ?I like this one especially. Thanks. -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
On 12 Dec 2015, at 2:42pm, Cecil Westerhof wrote: >> SELECT >>(SELECT COUNT(*) FROM proverbs) AS Total >>, (SELECT COUNT(*) FROM proverbs WHERE used IS NOT NULL) AS Used >>, (SELECT COUNT(*) FROM proverbs WHERE used IS NULL) AS Free >> > > ?That is what I am using now, but it is inefficient. This uses three scans > instead of two.? Do you have an index on the column 'used' ? Simon.
[sqlite] Selecting total and not used in one query
2015-12-12 19:00 GMT+01:00 Simon Slavin : > > On 12 Dec 2015, at 2:42pm, Cecil Westerhof wrote: > > >> SELECT > >>(SELECT COUNT(*) FROM proverbs) AS Total > >>, (SELECT COUNT(*) FROM proverbs WHERE used IS NOT NULL) AS Used > >>, (SELECT COUNT(*) FROM proverbs WHERE used IS NULL) AS Free > >> > > > > ?That is what I am using now, but it is inefficient. This uses three > scans > > instead of two.? > > Do you have an index on the column 'used' ? ?Not at the moment. But maybe that is a good idea.? -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
On 12 Dec 2015, at 6:14pm, Cecil Westerhof wrote: > ?Not at the moment. But maybe that is a good idea.? With clauses like >>> WHERE used IS NOT NULL) AS Used >>> WHERE used IS NULL It should transform the work done. Simon.
[sqlite] website documentation wording
On 12 Dec 2015, at 6:19pm, Mohit Sindhwani wrote: > Well, at least as far as SQLite goes, I think this settles it unambiguously > for me: > https://www.youtube.com/watch?v=giAMt8Tj-84 "As soon as I have a person need for Foreign Keys I'm sure you'll see them come in pretty quick." -- Richard Hipp, 2007. Simon.
[sqlite] Selecting total and not used in one query
2015-12-12 19:17 GMT+01:00 Simon Slavin : > > On 12 Dec 2015, at 6:14pm, Cecil Westerhof wrote: > > > ?Not at the moment. But maybe that is a good idea.? > > With clauses like > > >>> WHERE used IS NOT NULL) AS Used > > >>> WHERE used IS NULL > > It should transform the work done. > ?Here http://www.tutorialspoint.com/sqlite/sqlite_indexes.htm it is said that you should not use an index on columns that use a high number of NULL values. At the moment that is true. So I should not use an Index?? -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
On 12 Dec 2015, at 7:52pm, Cecil Westerhof wrote: > ?Here http://www.tutorialspoint.com/sqlite/sqlite_indexes.htm it is said > that you should not use an index on columns that use a high number of NULL > values. At the moment that is true. So I should not use an Index?? The tutorial thinks that you would use the index to pick out specific values in the column. Your own use is to differentiate between columns which have values and those which don't. Try it. Create the index. I promise it won't corrupt your database. Check to see whether it improves your times for the SELECT I quoted. If it turns out not to improve things then you can delete the index with no harm done. Simon.
[sqlite] Selecting total and not used in one query
2015-12-12 21:10 GMT+01:00 Simon Slavin : > > On 12 Dec 2015, at 7:52pm, Cecil Westerhof wrote: > > > ?Here http://www.tutorialspoint.com/sqlite/sqlite_indexes.htm it is said > > that you should not use an index on columns that use a high number of > NULL > > values. At the moment that is true. So I should not use an Index?? > > The tutorial thinks that you would use the index to pick out specific > values in the column. Your own use is to differentiate between columns > which have values and those which don't. > > Try it. Create the index. I promise it won't corrupt your database. > Check to see whether it improves your times for the SELECT I quoted. If it > turns out not to improve things then you can delete the index with no harm > done. > ?At the moment the select takes most of the time one millisecond. ?So I am afraid I will not see an improvement. But in the future I probably also want to select on date (what will be in used when it is not NULL), so for then it would be handy to have an index. Well lets just do it. ;-) It is a bit strange: after creating the index the database is still 176 KB. (It is a small one: I just started.) I would have expected the index to make the file bigger. Also now: SELECT Total , Used , Total - Used as Free FROM ( SELECT COUNT(*) AS Total , COUNT(used) AS Used FROM proverbs ) is faster as: SELECT COUNT(*) AS Total , SUM(used IS NOT NULL) AS Used , SUM(used IS NULL) AS Free FROM proverbs While before the index it was the other way around. It looks like the first is speeded up and the second slowed down. But it is nothing to really worry about I think. -- Cecil Westerhof
[sqlite] Putting an index on a boolean
I am playing with SQLite. I am thinking about writing an application for projects. At the moment I have the following: CREATE TABLE `projects` ( `projectID` TEXTPRIMARY KEY, `groupID` TEXT, `isPersonal`INTEGER NOT NULL CHECK(isPersonal in (0, 1)), `name` TEXT, `description` TEXT, `outcome` TEXT ); CREATE INDEX projects_groupID_idx ON projects(groupID); CREATE INDEX projects_isPersonal_idx ON projects(isPersonal); ?I like to differentiate between personal and non personal projects. Is it a good idea to put a index on isPersonal? By the way: I am thinking about using UUID for projectID and groupID, but I heard somewhere that it was a bad idea to use UUID for an indexed field. Is this true?? -- Cecil Westerhof
[sqlite] Putting an index on a boolean
On 12/12/15, Cecil Westerhof wrote: > I am playing with SQLite. I am thinking about writing an application for > projects. At the moment I have the following: > > CREATE TABLE `projects` ( > `projectID` TEXTPRIMARY KEY, > `groupID` TEXT, > `isPersonal`INTEGER NOT NULL CHECK(isPersonal in (0, 1)), > `name` TEXT, > `description` TEXT, > `outcome` TEXT > ); > CREATE INDEX projects_groupID_idx > ON projects(groupID); > CREATE INDEX projects_isPersonal_idx > ON projects(isPersonal); > > ?I like to differentiate between personal and non personal projects. Is it > a good idea to put a index on isPersonal? No, usually not. An exception would be if the boolean is almost always true or almost always false. Then using a partial index on (the infrequent value of) that boolean might make sense. CREATE TABLE t1( id INTEGER PRIMARY KEY, isPersonal BOOLEAN -- usually false ); CREATE INDEX t1x ON t1(isPersonal) WHERE isPersonal; Then queries of the form: SELECT * FROM t1 WHERE isPersonal; Would use the index, but the index will not take up much space. > > > By the way: I am thinking about using UUID for projectID and groupID, but I > heard somewhere that it was a bad idea to use UUID for an indexed field. Is > this true?? > I think you might have misunderstood. UUID is almost always a good field to index. One other point: The use of grave accents to quote column names is a mysql-ism. SQLite also supports that for compatibility. But you still shouldn't do it. The proper SQL-standard way is double-quote. -- D. Richard Hipp drh at sqlite.org
[sqlite] Putting an index on a boolean
2015-12-12 21:45 GMT+01:00 Richard Hipp : > On 12/12/15, Cecil Westerhof wrote: > > I am playing with SQLite. I am thinking about writing an application for > > projects. At the moment I have the following: > > > > CREATE TABLE `projects` ( > > `projectID` TEXTPRIMARY KEY, > > `groupID` TEXT, > > `isPersonal`INTEGER NOT NULL CHECK(isPersonal in (0, 1)), > > `name` TEXT, > > `description` TEXT, > > `outcome` TEXT > > ); > > CREATE INDEX projects_groupID_idx > > ON projects(groupID); > > CREATE INDEX projects_isPersonal_idx > > ON projects(isPersonal); > > > > ?I like to differentiate between personal and non personal projects. Is > it > > a good idea to put a index on isPersonal? > > No, usually not. An exception would be if the boolean is almost > always true or almost always false. Then using a partial index on > (the infrequent value of) that boolean might make sense. > ?I do not think that will be the case. It is even possible that one time it is mostly true and another time mostly false. I will remove the index. > > By the way: I am thinking about using UUID for projectID and groupID, > but I > > heard somewhere that it was a bad idea to use UUID for an indexed field. > Is > > this true?? > > > > I think you might have misunderstood. UUID is almost always a good > field to index. > ?I was told because of the nature of random UUID (what I will be using) it is hard to create a good index. The article said that data that is really random cannot be indexed very efficient. But I do not have to worry about it then. :-) It has been a few years back, so it is also possible that the problem is solved nowadays. ? > One other point: The use of grave accents to quote column names is a > mysql-ism. SQLite also supports that for compatibility. But you > still shouldn't do it. The proper SQL-standard way is double-quote. > ?That is funny: I did not use them at first (or double). But I am using 'DB Browser for SQLite' and this shows those, so I thought that ? ?I should use them.? -- Cecil Westerhof
[sqlite] Integrity check with a SQL command
I have the following tables: CREATE TABLE "projects" ( "projectID" TEXT PRIMARY KEY, "groupID" TEXT, "isPersonal"INTEGER NOT NULL CHECK(ispersonal in (0, 1)), "name" TEXT, "description" TEXT, "outcome" TEXT ); CREATE INDEX projects_groupID_idx ON projects(groupID); CREATE TABLE "subprojects" ( "subprojectID" TEXT PRIMARY KEY, "projectID" TEXT, "parentID" TEXT, "name" TEXT, "description" TEXT, "outcome" TEXT ); CREATE INDEX subprojects_projectID_idx ON projects(projectID); CREATE INDEX subprojects_parentID_idx ON subprojects(parentID); ?The idea is that a project can have several subprojects. And a subproject can also have several subprojects. Is there a way to use a SQL statement to verify that the data is not corrupt? (Everything should be a tree.) Also is there a SQL command to verify the debt is not more as for example five?? -- Cecil Westerhof
[sqlite] Putting an index on a boolean
On 2015-12-12 12:56 PM, Cecil Westerhof wrote: >>> By the way: I am thinking about using UUID for projectID and groupID, >> but I >>> heard somewhere that it was a bad idea to use UUID for an indexed field. >> Is >>> this true?? >> >> I think you might have misunderstood. UUID is almost always a good >> field to index. > > ?I was told because of the nature of random UUID (what I will be using) it > is hard to create a good index. The article said that data that is really > random cannot be indexed very efficient. But I do not have to worry about > it then. :-) It has been a few years back, so it is also possible that the > problem is solved nowadays. Cecil, it isn't about randomness, it is about uniqueness or cardinality. The fields that index the best are ones with many different values, in particular key fields where every record has a different value from every other record. UUIDs have this quality in spades. It is even more important to index such fields if you will either be searching/filtering on them or if they are the parent in a foreign key constraint. This has always been the case, its not a new thing. -- Darren Duncan
[sqlite] Putting an index on a boolean
Though to the extent that speed is proportional to data size, it would be good to use something other than hexadecimal to store UUIDs. Binary blobs would be the most compact, but ASCII85 encoding would work well if you need strings. Also, if these values are reused repeatedly as I suspect projectID and groupID might be, then it may be useful to intern them into a table and use integer keys. We got a noticeable performance improvement when I made that sort of change recently in our project. (I also implemented a string-to-integer-to-string cache that sits ahead of hitting the database.) Mark > On Dec 12, 2015, at 1:07 PM, Darren Duncan wrote: > > On 2015-12-12 12:56 PM, Cecil Westerhof wrote: By the way: I am thinking about using UUID for projectID and groupID, >>> but I heard somewhere that it was a bad idea to use UUID for an indexed field. >>> Is this true?? >>> >>> I think you might have misunderstood. UUID is almost always a good >>> field to index. >> >> ?I was told because of the nature of random UUID (what I will be using) it >> is hard to create a good index. The article said that data that is really >> random cannot be indexed very efficient. But I do not have to worry about >> it then. :-) It has been a few years back, so it is also possible that the >> problem is solved nowadays. > > Cecil, it isn't about randomness, it is about uniqueness or cardinality. The > fields that index the best are ones with many different values, in particular > key fields where every record has a different value from every other record. > UUIDs have this quality in spades. It is even more important to index such > fields if you will either be searching/filtering on them or if they are the > parent in a foreign key constraint. This has always been the case, its not a > new thing. -- Darren Duncan > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Integrity check with a SQL command
On 12/12/15, Cecil Westerhof wrote: > I have the following tables: > CREATE TABLE "projects" ( > "projectID" TEXT PRIMARY KEY, > ); > CREATE INDEX subprojects_projectID_idx > ON projects(projectID); Don't create indexes on primary keys. Doing so still gives a correct answer, but it wastes space and CPU cycles. -- D. Richard Hipp drh at sqlite.org
[sqlite] Putting an index on a boolean
2015-12-12 22:07 GMT+01:00 Darren Duncan : > On 2015-12-12 12:56 PM, Cecil Westerhof wrote: > >> By the way: I am thinking about using UUID for projectID and groupID, >>> but I >>> heard somewhere that it was a bad idea to use UUID for an indexed field. >>> Is >>> this true?? >>> >>> I think you might have misunderstood. UUID is almost always a good >>> field to index. >>> >> >> ?I was told because of the nature of random UUID (what I will be using) it >> is hard to create a good index. The article said that data that is really >> random cannot be indexed very efficient. But I do not have to worry about >> it then. :-) It has been a few years back, so it is also possible that the >> problem is solved nowadays. >> > > Cecil, it isn't about randomness, it is about uniqueness or cardinality. > The fields that index the best are ones with many different values, in > particular key fields where every record has a different value from every > other record. UUIDs have this quality in spades. It is even more important > to index such fields if you will either be searching/filtering on them or > if they are the parent in a foreign key constraint. This has always been > the case, its not a new thing. > ?That was what that (old) article said: because the data was completely random it was hard to create a balanced tree for the index. I did find it a little strange, but I am not an expert on creating balanced trees for an index. But again: I am happy that it is not a point. -- Cecil Westerhof
[sqlite] Putting an index on a boolean
For my part, in a database I designed that used a SHA-256 hash for a unique identifier that was then a foreign key from many other tables, I stored that as an integer and not as a hex string. If UUIDs are similarly numbers fundamentally, they possibly could do likewise. I agree with Mark's comment re binary. -- Darren Duncan On 2015-12-12 1:12 PM, Mark Hamburg wrote: > Though to the extent that speed is proportional to data size, it would be > good to use something other than hexadecimal to store UUIDs. Binary blobs > would be the most compact, but ASCII85 encoding would work well if you need > strings. > > Also, if these values are reused repeatedly as I suspect projectID and > groupID might be, then it may be useful to intern them into a table and use > integer keys. We got a noticeable performance improvement when I made that > sort of change recently in our project. (I also implemented a > string-to-integer-to-string cache that sits ahead of hitting the database.) > > Mark > >> On Dec 12, 2015, at 1:07 PM, Darren Duncan >> wrote: >> >> On 2015-12-12 12:56 PM, Cecil Westerhof wrote: > By the way: I am thinking about using UUID for projectID and groupID, but I > heard somewhere that it was a bad idea to use UUID for an indexed field. Is > this true?? I think you might have misunderstood. UUID is almost always a good field to index. >>> >>> ?I was told because of the nature of random UUID (what I will be using) it >>> is hard to create a good index. The article said that data that is really >>> random cannot be indexed very efficient. But I do not have to worry about >>> it then. :-) It has been a few years back, so it is also possible that the >>> problem is solved nowadays. >> >> Cecil, it isn't about randomness, it is about uniqueness or cardinality. >> The fields that index the best are ones with many different values, in >> particular key fields where every record has a different value from every >> other record. UUIDs have this quality in spades. It is even more important >> to index such fields if you will either be searching/filtering on them or if >> they are the parent in a foreign key constraint. This has always been the >> case, its not a new thing. -- Darren Duncan
[sqlite] Putting an index on a boolean
2015-12-12 22:12 GMT+01:00 Mark Hamburg : > Though to the extent that speed is proportional to data size, it would be > good to use something other than hexadecimal to store UUIDs. Binary blobs > would be the most compact, but ASCII85 encoding would work well if you need > strings. > > Also, if these values are reused repeatedly as I suspect projectID and > groupID might be, then it may be useful to intern them into a table and use > integer keys. We got a noticeable performance improvement when I made that > sort of change recently in our project. (I also implemented a > string-to-integer-to-string cache that sits ahead of hitting the database.) > ?That was something I was wondering about. I was saving this question for when I got further ahead. But it does not hurt to have info about this. :-D
[sqlite] Integrity check with a SQL command
2015-12-12 22:13 GMT+01:00 Richard Hipp : > On 12/12/15, Cecil Westerhof wrote: > > I have the following tables: > > CREATE TABLE "projects" ( > > "projectID" TEXT PRIMARY KEY, > > ); > ?? > > CREATE INDEX subprojects_projectID_idx > > ON projects(projectID); > > Don't create indexes on primary keys. Doing so still gives a correct > answer, but it wastes space and CPU cycles. > ?Stupid error. :'-( Should be: ?CREATE INDEX subprojects_projectID_idx ON subprojects(projectID);? -- Cecil Westerhof
[sqlite] Putting an index on a boolean
The first question(s) I would ask are: Are all the fields case sensitive? (according to your definition they are) Are any of them, other than the primary key, unique? (according to your definition they are not) Other than the isPersonal column all of the columns permit a NULL entry. Is this your intent (it is what is written) One would presume (based on the English meanings of the column names) that the groupid groups multiple projects together. How do you intend to insure the group name is correct since you are repeating it in every record. Do you not think it may be more appropriate to have a table of groups, and have the projects.groupid be a foreign key into the groups table? Using "big long inscrutable strings or binary blobs" for keys is relatively unimportant compared to answering the questions above. You could also just use great big long strings of random data as keys (which is what a uuid is). It will only serve to make it difficult for a human to debug your database. If you are going to do that, then create a separate table of uuid to simple-integer-sequence keys. That way the uuids are kept where they belong, completely outside of the need for human viewing. Yet you can still use them as unique long strings to attach to simple abstract keys if you are so addicted. (I have a set of big rusty pinking shears for use on people who use uuid's as keys. It is almost impossible to "just look" at something that uses uuids as keys and be able to tell what you are looking at (though it is a little better on my 104" monitor, but it is still impossible to "see" relations. Using simple small integers is far better, though YMMV if you have eidetic memory.) > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Cecil Westerhof > Sent: Saturday, 12 December, 2015 15:32 > To: SQLite mailing list > Subject: [sqlite] Putting an index on a boolean > > I am playing with SQLite. I am thinking about writing an application for > projects. At the moment I have the following: > > CREATE TABLE `projects` ( > `projectID` TEXTPRIMARY KEY, > `groupID` TEXT, > `isPersonal`INTEGER NOT NULL CHECK(isPersonal in (0, 1)), > `name` TEXT, > `description` TEXT, > `outcome` TEXT > ); > CREATE INDEX projects_groupID_idx > ON projects(groupID); > CREATE INDEX projects_isPersonal_idx > ON projects(isPersonal); > > ?I like to differentiate between personal and non personal projects. Is it > a good idea to put a index on isPersonal? > > > By the way: I am thinking about using UUID for projectID and groupID, but > I > heard somewhere that it was a bad idea to use UUID for an indexed field. > Is > this true?? > > -- > Cecil Westerhof > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Putting an index on a boolean
> > One other point: The use of grave accents to quote column names is a > > mysql-ism. SQLite also supports that for compatibility. But you > > still shouldn't do it. The proper SQL-standard way is double-quote. > ?That is funny: I did not use them at first (or double). But I am using 'DB > Browser for SQLite' and this shows those, so I thought that > ?I should use them.? Like the Windows Veneer of Long File Names and permitting embedded "special characters" in filenames, you only need to use quotes identifying field names if you are making the atrocious error of using restricted symbols or words as column names. If you do not do that, then you do not need them.
[sqlite] Putting an index on a boolean
2015-12-12 22:44 GMT+01:00 Keith Medcalf : > > The first question(s) I would ask are: > Are all the fields case sensitive? (according to your definition they are) > ?Do you mean the name or the contents? ? > Are any of them, other than the primary key, unique? (according to your > definition they are not) > ?Only the primary key is unique. Maybe I should make name unique to, but it could be possible to have to projects with the same name in different groups I think. ? > Other than the isPersonal column all of the columns permit a NULL entry. > Is this your intent (it is what is written) > ?When thinking more about it, that should only be groupID. I think the others should always be filled. ? > One would presume (based on the English meanings of the column names) > that the groupid groups multiple projects together. How do you intend to > insure the group name is correct since you are repeating it in every > record. Do you not think it may be more appropriate to have a table of > groups, and have the projects.groupid be a foreign key into the groups > table? > ?I have a groups table also. But did not show it, because I did not think it important. It is: CREATE TABLE "groups" ( "groupID" TEXT PRIMARY KEY, "name"TEXT ); And probably I should make name unique.? -- Cecil Westerhof
[sqlite] Putting an index on a boolean
2015-12-12 22:50 GMT+01:00 Keith Medcalf : > > > One other point: The use of grave accents to quote column names is a > > > mysql-ism. SQLite also supports that for compatibility. But you > > > still shouldn't do it. The proper SQL-standard way is double-quote. > > > ?That is funny: I did not use them at first (or double). But I am using > 'DB > > Browser for SQLite' and this shows those, so I thought that > > ?I should use them.? > > Like the Windows Veneer of Long File Names and permitting embedded > "special characters" in filenames, you only need to use quotes identifying > field names if you are making the atrocious error of using restricted > symbols or words as column names. If you do not do that, then you do not > need them. > ?I can go back to not using them. :-D -- Cecil Westerhof
[sqlite] I have no idea what to install!
Please forgive my lack of knowledge but for the life of me I can't figure out what to install or how to install what I should be using. I'm using VS2015 Community edition where I'm attempting to create a WPF desktop application for windows (not specific to 8 or 10). I have a rather basic understanding of VB. Right now I am just trying to use a DataGrid to display the data from the database. I would truly appreciate any help at all!!
[sqlite] I have no idea what to install!
On 2015-12-12 4:02 PM, Nicolette Warner wrote: > Please forgive my lack of knowledge but for the life of me I can't figure > out what to install or how to install what I should be using. I'm using > VS2015 Community edition where I'm attempting to create a WPF desktop > application for windows (not specific to 8 or 10). I have a rather basic > understanding of VB. Right now I am just trying to use a DataGrid to > display the data from the database. I would truly appreciate any help at > all!! That's kind of broad. I find in situations like this the best bet is to try Google searching a few relevant terms together, for example "SQLite visual studio 2015 datagrid" and so on. Unless this is super-new, you're likely to find either manual pages or a forum such as stack overflow where someone asked a related thing already. You're likely to find answers with less effort on everyone's part if you do that. -- Darren Duncan
