Re: [sqlite] Question about floating point
On Sat, 15 Dec 2018 at 15:10, Frank Millman wrote: > On Dec 15, 2018, at 08.58, Jay Kreibich wrote: > > > > On Dec 15, 2018, at 12:49 AM, Frank Millman > wrote: > > > > > > I know that floating point is not precise and not suitable for > financial uses. Even so, I am curious about the following - > > > > [...] > > > > > > With the same version of sqlite3 and the same select statement, why > does python return a different result from sqlite3.exe? > > > > Because the shell is altering the output to make it easier to read. > Consider: > > > > sqlite> select 211496.252; > > 211496.26 > I just wanted to point out that python does the same thing (as does basically every floating point display routine): $ python Python 2.7.5 (default, Aug 4 2017, 00:39:18) [GCC 4.8.5 20150623 (Red Hat 4.8.5-16)] on linux2 >>> a=211496.252 >>> a 211496.252 >>> print a 211496.26 >>> repr(a) '211496.252' >>> str(a) '211496.26' It's just that the python interpreter outputs the "representation" by default. (Interesting thread; wow at the uₙ = 111 - 1130/uₙ₋₁ + 3000/(uₙ₋₁·uₙ₋₂) sequence!) -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Question] Non-EXCLUSIVE modes in dot-file locking strategy
On Sun, 16 Dec 2018 at 05:00, Pierre Tempel wrote: > > “... programs which rely on [the O_CREAT and O_EXCL flags of > > open(2) to work on filesystems accessed via NFS version 2] for > > performing locking tasks will contain a race condition. The solution > > for performing atomic file locking using a lockfile is to create a > > unique file on the same filesystem (e.g., incorporating hostname and > > pid), use link(2) to make a link to the lockfile and use stat(2) on > > the unique file to check if its link count has increased to 2. Do not > > use the return value of the link(2) call.” > > > > The question is if this applies to current NFS versions (3, 4). > However, If I read this correctly, the link count strategy can be used > to encode more info. However, this again depends on OS-specific > extended attributes, which is contrary to the general file-system based > locking I was looking to create. > > As an aside, I don't quite understand why and how a reader/writer lock > requires/uses a counter. I'd appreciate a link to any documentation > about that. > For a reader/writer lock, the usual semantics allow a single writer but arbitrarily many readers. However we're in a situation where we don't have actual locking primitives. ie. each reader will need its own lockfile - they can't share one - and this the problem a counter addresses (by providing unique files). Of course it also creates another problem, as now how do you atomically check whether anyone has a read lock? Or prevent two processes simultaneously creating a read-lockfile and a write-lockfile? Encoding information into the attributes of a common lockfile seems to suffer similar race conditions, but lets say there's a way to do it. Now one process comes and starts a transaction; obtains a read lock. While it's busy a second process comes and also acquires a read lock. The second query finishes really quick and goes to relinquish its read lock. However it does this, it must leave the overall database in a read-locked state, because the first process is still busy! How would you achieve that without a counter or equivalent mechanism? -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW
Am Do., 27. Dez. 2018 um 02:53 Uhr schrieb Mark Johnson < mj10...@googlemail.com>: > (summery of the last messages that were sent as email) > > >> Please add the list of column names after the view name: > >> CREATE VIEW middle_earth_admin_general(a,b,c,e) AS ... > >> I have a note to improve the documentation about this point. > > So would the following be true: > > To insure that a constant, proper column resolvement, VIEWs should be > defined in a similar way as an INSERT command where a sub-set of columns > with values is done: > > INSERT INTO gcp_master > (name, longitude,latitude) > SELECT > name, longitude,latitude > FROM populated_places > WHERE name LIKE "roma,%"; > > > CREATE VIEW gcp_master_view > (name, longitude,latitude) AS > SELECT > name, longitude,latitude > FROM populated_places > WHERE name LIKE "roma,%"; > > > --- Final note: > > When creating a VIEW with a list of defined column names, a COLUMN rename > on the underlining TABLE: > > ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "longitude" TO > "position_x"; > ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "latitude" TO > "position_y"; > Correction: ALTER TABLE "main"."populated_places" RENAME COLUMN "longitude" TO "position_x"; ALTER TABLE "main"."populated_places" RENAME COLUMN "latitude" TO "position_y"; > > will result will be: > - rename of the COLUMN of the TABLE > - rename of the referenced TABLE in the corresponding VIEWs and TRIGGERs > - will NOT rename the COLUMN definition of the VIEW > > The final CREATE command will then look like this: > > CREATE VIEW gcp_master_view > (name, longitude,latitude) AS > SELECT > name, "position_x","position_y" > FROM populated_places > WHERE name LIKE "roma,%"; > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW
(summery of the last messages that were sent as email) >> Please add the list of column names after the view name: >> CREATE VIEW middle_earth_admin_general(a,b,c,e) AS ... >> I have a note to improve the documentation about this point. So would the following be true: To insure that a constant, proper column resolvement, VIEWs should be defined in a similar way as an INSERT command where a sub-set of columns with values is done: INSERT INTO gcp_master (name, longitude,latitude) SELECT name, longitude,latitude FROM populated_places WHERE name LIKE "roma,%"; CREATE VIEW gcp_master_view (name, longitude,latitude) AS SELECT name, longitude,latitude FROM populated_places WHERE name LIKE "roma,%"; --- Final note: When creating a VIEW with a list of defined column names, a COLUMN rename on the underlining TABLE: ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "longitude" TO "position_x"; ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "latitude" TO "position_y"; will result will be: - rename of the COLUMN of the TABLE - rename of the referenced TABLE in the corresponding VIEWs and TRIGGERs - will NOT rename the COLUMN definition of the VIEW The final CREATE command will then look like this: CREATE VIEW gcp_master_view (name, longitude,latitude) AS SELECT name, "position_x","position_y" FROM populated_places WHERE name LIKE "roma,%"; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unable to import CSV file correctly when using ext/misc/csv.c as the csv module.
There is also a problem with the documentation on csv.c Example from the source comments: CREATE VIRTUAL TABLE temp.csv2 USING csv( filename = "../http.log", schema = "CREATE TABLE x(date,ipaddr,url,referrer,userAgent)" ); It should be noted that the schema= parameter allows one to specify names, NOT types. Everything is set to a datatype of TEXT. Any datatype declarations are ignored. so with "CREATE VIRTUAL TABLE temp.csv2 USING csv( filename = "../http.log", schema = "CREATE TABLE x(anbr INTEGER ,ipaddr,url,referrer,userAgent)" ); typeof (anbr) is "text" ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW
On 12/26/18, Mark Johnson wrote: > Am Do., 20. Dez. 2018 um 16:34 Uhr schrieb Mark Johnson < > mj10...@googlemail.com>: > >> Based on ticket >> >> https://www.sqlite.org/src/tktview?name=43ddc85a63 >> >> >> >> However, the column count is not correct. >> In my case 2 columns are missing: which should be 19. >> > After a fresh look at this today, I realized that this error was caused by > a faulty VIEW, where the 2 columns were not defined in the VIEW but being > used in the TRIGGER. > > After correcting the VIEW, the error is different: > > ALTER TABLE "main"."middle_earth_admin" RENAME COLUMN "admin_type" TO > "admin_level"; I closed the original ticket (which I neglected to do originally) with an explanation of why it is not a bug. In order for us to investigate this new problem you are having, please supply us with the VIEW, CREATE TRIGGER, and ALTER TABLE statements that are giving you trouble. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW
Am Do., 20. Dez. 2018 um 16:34 Uhr schrieb Mark Johnson < mj10...@googlemail.com>: > Based on ticket > > https://www.sqlite.org/src/tktview?name=43ddc85a63 > > > > However, the column count is not correct. > In my case 2 columns are missing: which should be 19. > After a fresh look at this today, I realized that this error was caused by a faulty VIEW, where the 2 columns were not defined in the VIEW but being used in the TRIGGER. After correcting the VIEW, the error is different: ALTER TABLE "main"."middle_earth_admin" RENAME COLUMN "admin_type" TO "admin_level"; The lookupName parameter 'zCol' contains the new column name 'admin_level', but is searching for the old column name admin_type, which is not being found. -I-> lookupName -200a- looking_for[NEW.admin_type] pParse->eTriggerOp[116] pTriggerTab->zName[middle_earth_admin_general] -I-> lookupName -201a- looking_for[NEW.admin_type] op[116 !=TK_DELETE[117]] pTriggerTab->zName[middle_earth_admin_general] -I-> lookupName -202a loop - pCol->zName[admin_type]==zCol[admin_level] iCol[3] Error: near line 14: error in trigger vw_ins_middle_earth_admin_general after rename: no such column: NEW.admin_type > > > Mark Johnson > mj10...@googlemail.com > > > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unable to import CSV file correctly when using ext/misc/csv.c as the csv module.
Fixed at https://www.sqlite.org/src/info/7acaed08f946633f On 12/25/18, hi jack wrote: > The SQLite source code version I’m using is the latest public release > version 3.26.0. > > -- > > Reproduce steps: > > 1. Prepare a file data.csv. The content is > a,b,c,d > 1,2,3,4 > 1,2,3,4 > 1,2,3,4 > > 2. Run this query to import csv data. Only two parameters has been > specified: filename and header. > CREATE VIRTUAL TABLE temp.t1 USING csv(filename='data.csv', header); > SELECT * FROM t1; > > 3. > Expected result in Step 2 is > a,b,c,d > 1,2,3,4 > 1,2,3,4 > 1,2,3,4 > > Actual result in Step 2 is > a,b,c,d > There’s only header be imported, table content is lost. > > -- > > I think this issue is caused by this line of code in the file > ext/misc/csv.c > (https://www.sqlite.org/cgi/src/artifact/88333dc9f7dcf6a8), > > pNew->iStart = ftell(sRdr.in); > > which it might should be > > pNew->iStart = ftell(sRdr.in) - sRdr.nIn + sRdr.iIn; > > And I may wrong about the fixing, but this bug is exists in real. > -- > > Best regards. > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unable to import CSV file correctly when using ext/misc/csv.c as the csv module.
The SQLite source code version I’m using is the latest public release version 3.26.0. -- Reproduce steps: 1. Prepare a file data.csv. The content is a,b,c,d 1,2,3,4 1,2,3,4 1,2,3,4 2. Run this query to import csv data. Only two parameters has been specified: filename and header. CREATE VIRTUAL TABLE temp.t1 USING csv(filename='data.csv', header); SELECT * FROM t1; 3. Expected result in Step 2 is a,b,c,d 1,2,3,4 1,2,3,4 1,2,3,4 Actual result in Step 2 is a,b,c,d There’s only header be imported, table content is lost. -- I think this issue is caused by this line of code in the file ext/misc/csv.c (https://www.sqlite.org/cgi/src/artifact/88333dc9f7dcf6a8), pNew->iStart = ftell(sRdr.in); which it might should be pNew->iStart = ftell(sRdr.in) - sRdr.nIn + sRdr.iIn; And I may wrong about the fixing, but this bug is exists in real. -- Best regards. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple way to import GPX file?
I know I am coming to this a couple of weeks late, but I have been doing this for several years and thought I would add my 2 cents worth. Probably too late for OP, but may be useful for someone else later. I used gpsbabel like this initially (in a JPSoft 4nt/tcmd script), gpsbabel -i gpx ^ -f %fname ^ -x nuketypes,tracks,routes ^ -o xcsv,style=G7W-xcsv.style ^ -F "%@name[%fname].csv" %fname - variable containing source file name %@name[ ... ] - function to extract basename from full filename with this style sheet to generate .csv files in my desired format. # gpsbabel XCSV style file # # Format: G7toWin csv format # Author: John McMahon # Date: 2005may24 # Update: 2006jun02jmcm # DESCRIPTION G7toWin csv file format # # FILE LAYOUT DEFINITIONS # FIELD_DELIMITER COMMA RECORD_DELIMITER NEWLINE BADCHARS COMMA SHORTLEN 10 PROLOGUE Version 2:CSV PROLOGUE Datum:,WGS-84 PROLOGUE ZoneOffset:,0.00 PROLOGUE "Type","Name","Lat","Long","Month\#","Day#","Year","Hour","Min","Sec","Comment","Symbol#","SymbolColor","SymbolDisplay","Altitude (Meters)","Depth (Meters)","Ref Dist","Ref units" # # INDIVIDUAL DATA FIELDS, IN ORDER OF APPEARANCE # IFIELD CONSTANT, "W", "%s"# "Type", IFIELD SHORTNAME, "", "%s"# "Name", IFIELD LAT_DECIMAL, "", "%f"# "Lat", IFIELD LON_DECIMAL, "", "%f"# "Long", IFIELD IGNORE,"", "%s"# "Month#", IFIELD IGNORE,"", "%s"# "Day#", IFIELD IGNORE,"", "%s"# "Year", IFIELD IGNORE,"", "%s"# "Hour", IFIELD IGNORE,"", "%s"# "Min", IFIELD IGNORE,"", "%s"# "Sec", IFIELD IGNORE,, "", "%s"# "Comment", IFIELD IGNORE,"", "%s"# "Symbol#", IFIELD IGNORE,"", "%s"# "SymbolColor", IFIELD IGNORE,"", "%s"# "SymbolDisplay", IFIELD IGNORE,"", "%s"# "Altitude (Meters)", IFIELD IGNORE,"", "%s"# "Depth (Meters)", IFIELD IGNORE,"", "%s"# "Ref Dist", IFIELD IGNORE,"", "%s"# "Ref units" However, I have recently replaced that with a perl script using the Geo::GPX module. John On 10/12/2018 10:17, no...@null.net wrote: On Sun Dec 09, 2018 at 03:16:15PM -0700, Winfried wrote: Good call, thank you. For others' benefit: 1. Copy the file, open the copy in a text editor, use a regex to turn the data into tab-separated columns If you are running some kind of unix-like environment this is something Perl can be quite useful for: grep '^(.*)!$1\t$2\t$3!' \ > waypoints.tsv 2. Create a new file, and create the table: sqlite3 waypoints.sqlite sqlite> CREATE TABLE waypoints (name text, latitude text, longitude text, id INTEGER PRIMARY KEY); 3. Import data: sqlite> .separator "\t" sqlite> .import waypoints.tsv waypoints select * from waypoints where id=1; -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Typo
Thanks. Fixed at https://www.sqlite.org/docsrc/info/2ffc105f81a74dad and on the website. On 12/26/18, Wolfgang Enzinger wrote: > > Another one in https://www.sqlite.org/security.html: > > "... even with presented with maliciously malformed SQL inputs or database > files." > > Should be "... even when presented with ..." IMO. > > Wolfgang > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Typo
Am Tue, 25 Dec 2018 08:59:45 +0100 schrieb gwenn: > There is a typo here: > https://sqlite.org/session/sqlite3changeset_op.html >> If pbIncorrect is not NULL, then *pbIndirect is set to true > Should be pbIndirect instead of pbIncorrect. Another one in https://www.sqlite.org/security.html: "... even with presented with maliciously malformed SQL inputs or database files." Should be "... even when presented with ..." IMO. Wolfgang ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] i Know i should use 'AS', but ....
On 26-12-2018 08:20, Clemens Ladisch wrote: Luuk wrote: sqlite> .mode column sqlite> .headers on sqlite> select 1 as X,date() as d union all select 2,date() union all select 3,datetime(); X d -- -- 1 2018-12-25 2 2018-12-25 3 2018-12-25 The value is longer than the column with (which is probably based on the values in the first line). Try ".width 10 20". A, now i know why i do not use 'mode column' It's too much typing work to find out how wide the columns are. '.mode tabs' seems to fit my need more. Thanks! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
