Re: [sqlite] CSV import deletes trailing zeroes on text fields
1. Rename .csv to .txt2. Excel will now ask for column treatment on import3. Specify the problem column(s) as "text" not "general" There are other problems with csv recognizing text as numbers. I had a column with content, say 123D4. Excel recognized the old FORTRAN double precision format and called it 123. On Thursday, July 12, 2018, 2:39:09 PM PDT, David Burgess wrote: "CSV import deletes /leading/ zeroes on text fields" excel does this. Quite difficult to stop it from doing so. On Fri, Jul 13, 2018 at 6:52 AM, R Smith wrote: > I believe your subject should read: "CSV import deletes /leading/ zeroes on > text fields" - Your trailing Zero is in tact. > > And your declaration is wrong - in SQL the column name is first, then the > Type, so it must be: > CREATE TABLE foo(bar TEXT NOT NULL); > > Opening the csv file in Excel or CALC will probably do the exact same thing > - but SQLite should be better than that. > > > > On 2018/07/12 10:47 AM, Simon Leo Hafner wrote: >> >> To reproduce: >> >> create table foo ( >> text bar not null >> ); >> >> .import test.csv foo >> >> select * from foo; >> >> With test.csv: >> >> test >> 01230 >> >> Expected result: >> >> test >> 01230 >> >> Actual result: >> >> test >> 1230 >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CSV import deletes trailing zeroes on text fields
"CSV import deletes /leading/ zeroes on text fields" excel does this. Quite difficult to stop it from doing so. On Fri, Jul 13, 2018 at 6:52 AM, R Smith wrote: > I believe your subject should read: "CSV import deletes /leading/ zeroes on > text fields" - Your trailing Zero is in tact. > > And your declaration is wrong - in SQL the column name is first, then the > Type, so it must be: > CREATE TABLE foo(bar TEXT NOT NULL); > > Opening the csv file in Excel or CALC will probably do the exact same thing > - but SQLite should be better than that. > > > > On 2018/07/12 10:47 AM, Simon Leo Hafner wrote: >> >> To reproduce: >> >> create table foo ( >>text bar not null >> ); >> >> .import test.csv foo >> >> select * from foo; >> >> With test.csv: >> >> test >> 01230 >> >> Expected result: >> >> test >> 01230 >> >> Actual result: >> >> test >> 1230 >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please unsubscribe me. Thanks
Click on the link at the bottom of every post to the list, including this one, then scroll to the bottom of the page to find how to unsubscribe. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Please unsubscribe me. Thanks
___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CSV import deletes trailing zeroes on text fields
I believe your subject should read: "CSV import deletes /leading/ zeroes on text fields" - Your trailing Zero is in tact. And your declaration is wrong - in SQL the column name is first, then the Type, so it must be: CREATE TABLE foo(bar TEXT NOT NULL); Opening the csv file in Excel or CALC will probably do the exact same thing - but SQLite should be better than that. On 2018/07/12 10:47 AM, Simon Leo Hafner wrote: To reproduce: create table foo ( text bar not null ); .import test.csv foo select * from foo; With test.csv: test 01230 Expected result: test 01230 Actual result: test 1230 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CSV import deletes trailing zeroes on text fields
On 12 Jul 2018, at 9:47am, Simon Leo Hafner wrote: > create table foo ( > text bar not null > ); Should be bar TEXT NOT NULL I'm not sure how your line is being parsed, but I can understand it thinking you have not set a column type. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CSV import deletes trailing zeroes on text fields
To reproduce: create table foo ( text bar not null ); .import test.csv foo select * from foo; With test.csv: test 01230 Expected result: test 01230 Actual result: test 1230 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Network share & disk I/O error
On 12 Jul 2018, at 19:11, Richard Hipp wrote: > When network filesystems do not follow the usual semantics of a > filesystem, it can cause problems. > > That said, Firefox and Chrome have for many years stored lots of stuff > in SQLite database files in the users home directory, which is often a > network share, and they have not reported any problems. Hi, ok, that’s good news. So it’s definitely possible to get working. > On the other hand, they (or at least Firefox, I not sure about Chrome) run > with > PRAGMA locking_mode=EXCLUSIVE [1] which tends to bypass most of the > network filesystem bugs, at the expense of restricting database access > to a single process at a time. Ok, we can try that. > Is your system able to live with that restriction and use PRAGMA > locking_mode=EXCLUSIVE? Yes, that shouldn’t be a problem. Thanks. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Network share & disk I/O error
With Richards comment, the only additional thing I'd weigh in on is what your remote file server is doing with the physical file. Virus scanners can interfere with the usual operation of your program, and can cause certain things to happen. Because the OS you're running your software on is told that the file is written to successfully, it doesn't necessarily mean that the remote server has actually performed the work. There are a lot of new assumptions that are thrown into the mix when dealing with remote files. Windows machines are notorious for not allowing things to happen to files (IE: Delete/erase/rename/etc) while something external has hold of them, which is a pain when doing file maintenance on larger systems with lots of remote fingers on said file. Linux is more liberal with how it handles file access in that you can do more intentionally damaging things to files (Such as delete/erase/rename/etc) but the problem with that approach is that the client can never be sure its got the most recent data available because the filesystem on the remote end has done something different with the file compared to what your application is expecting, even with single user single connection access. On Thu, Jul 12, 2018 at 12:59 PM, Robert M. Münch < robert.mue...@saphirion.com> wrote: > Hi, > > Context: Users of our app can define a working directory where sqlite > files are stored. This can be a network share. The files are only used by > one user at the time. > > We now saw two crash reports with „Database disk I/O error“ when a > transaction was closed with „END“. Is this a known problem when having > sqlite files on a network share? > > I read about the locking quirks on network share but related this to cases > where a file could be accessed by several users at the same time and an > application trusts the operating system to handle the locking correct. I > don’t understand it that way that using sqlite files from a network share > is per se impossible. > > Any insights on this? > > Viele Grüsse. > > -- > > Robert M. Münch, CEO > M: +41 79 65 11 49 6 > > Saphirion AG > smarter | better | faster > > http://www.saphirion.com > http://www.nlpp.ch > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Round-tripping SQLite back and forth between text representation.
On 7/12/18, dmp wrote: > > I use a dump > in my interface which I used with diff to compare changes in my > personal expense database. This was to insure changes introduced in work > on the interface were not screwing things up. Very helpful to insure > your not introducing bugs. I am glad that has been working for you. But there is a caveat: The ".dump" format can (and does) change slightly from one release of SQLite to the next. So you are welcomed to continue using ".dump" this way, but just be careful that you do not compare the .dump output from two different versions of SQLite. -- 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] Network share & disk I/O error
On 7/12/18, Robert M. Münch wrote: > > We now saw two crash reports with „Database disk I/O error“ when a > transaction was closed with „END“. Is this a known problem when having > sqlite files on a network share? > When network filesystems do not follow the usual semantics of a filesystem, it can cause problems. That said, Firefox and Chrome have for many years stored lots of stuff in SQLite database files in the users home directory, which is often a network share, and they have not reported any problems. On the other hand, they (or at least Firefox, I not sure about Chrome) run with PRAGMA locking_mode=EXCLUSIVE [1] which tends to bypass most of the network filesystem bugs, at the expense of restricting database access to a single process at a time. Is your system able to live with that restriction and use PRAGMA locking_mode=EXCLUSIVE? [1] https://www.sqlite.org/pragma.html#pragma_locking_mode -- 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] Round-tripping SQLite back and forth between text representation.
Randall wrote: > My wishlist is: > (o) Allow humans to view the contents of a DB without custom tools. If what is meant here is a generic tool that opens/views any particular file format, db context here, then there are tools including the generic db gui that I have been working on for years. > (o) Have a way to see what has changed between V1 and V2 of a database, > e.g., for a "change review." > (o) Have a way to merge two independent sets of database changes into > a single result in an understandable way. This has already been answered, .dump diff and sqldiff. I use a dump in my interface which I used with diff to compare changes in my personal expense database. This was to insure changes introduced in work on the interface were not screwing things up. Very helpful to insure your not introducing bugs. > (o) Have a way to make changes (update, insert, delete) to the DB data in a pinch without specialized tools. My generic db gui will do all these. Once more it really is a plugin framework so it is very easy to introduce your own code to extend its behavior. Seems parts of the wishlist could be provided more effectly by client/server db rather than SQLite. My personal general expenses database is not SQLite, but a client/server db. Just an old box back in the corner. Why, because I use the same db server with my dad's, in his 90s, expenses which we both can add, edit, search, review, and aggregate accounts at the end of the year. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Network share & disk I/O error
Hi, Context: Users of our app can define a working directory where sqlite files are stored. This can be a network share. The files are only used by one user at the time. We now saw two crash reports with „Database disk I/O error“ when a transaction was closed with „END“. Is this a known problem when having sqlite files on a network share? I read about the locking quirks on network share but related this to cases where a file could be accessed by several users at the same time and an application trusts the operating system to handle the locking correct. I don’t understand it that way that using sqlite files from a network share is per se impossible. Any insights on this? Viele Grüsse. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] First question. Learning to use mailing lists
Your email to the list went through ok. As far as the list goes, only admin can see the whole list of who's subscribed. Otherwise you can only see who posts messages. An example as to why is that just recently we had an issue of a spam bot sending junk to anyone who posted to the list. If it had been able to get the list of everyone then it would have been way worse. Other notes for a newcomer to the list is that it is text only, and doesn't allow attachments. (Your message will go through but the attachments will be stripped before being sent to the group) The Well-Known Users page (https://www.sqlite.org/famous.html) lists a sample of companies that use SQLite. The short, short version is: It's everywhere and in everything. But there are also plenty of individual users who use it for their own one-offs and small personal projects. And this list will happily answer newbie questions as well as advanced specific questions. (We just won't do your homework for you) Welcome. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Enrique Mesa Sent: Thursday, July 12, 2018 11:27 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] First question. Learning to use mailing lists Hello. I am a newcommer to programming tools like GIT and everything. I have programming skills but i don't know how mailing lists works. I want to submit my own quesion. Is this being read by all members? Please, if someone read this, send a reply to me. Also, i want to have see portfolio of members, because i am a programmer but i remain theorical and don't build serious software. Just to enjoy software. I am from Venezuela, and my country is spanish speaker. My question is: I am want just to see portfolio of members, Just I want to see what is able to do SQLite. Pèrsonally I have used it in my custom software due to easy to set up without dealing with a mess like mysql that requires you to connect to a server-based software. Also, I plan to learn how to read its source code. Give me please a summary of your software which uses SQLite. Some nice videos, releases (demos or paid software), websites or any kind of content of your ownership is welcomed. Thanks, Enrique ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] First question. Learning to use mailing lists
Hello. I am a newcommer to programming tools like GIT and everything. I have programming skills but i don't know how mailing lists works. I want to submit my own quesion. Is this being read by all members? Please, if someone read this, send a reply to me. Also, i want to have see portfolio of members, because i am a programmer but i remain theorical and don't build serious software. Just to enjoy software. I am from Venezuela, and my country is spanish speaker. My question is: I am want just to see portfolio of members, Just I want to see what is able to do SQLite. Pèrsonally I have used it in my custom software due to easy to set up without dealing with a mess like mysql that requires you to connect to a server-based software. Also, I plan to learn how to read its source code. Give me please a summary of your software which uses SQLite. Some nice videos, releases (demos or paid software), websites or any kind of content of your ownership is welcomed. Thanks, Enrique ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lowering totalUsed
This query will work fine. You could also do something like: UPDATE tips SET totalUsed = totalUsed - (SELECT MIN(totalUsed) - 1 FROM tips); which would include the extra 1 (the new base) in the scalar subquery. The expression (SELECT MIN(totalUsed) FROM tips) is not correlated with the outer query (the update) and is a scalar value that is computed only once (when the first row of the outer update is processed), so the query effectively becomes equivalent to the following: begin; v = select min(totalUsed) from tips; update tips set totalUsed = totalused - v + 1; commit; where v would be passed in and out by your application, or, if you include the extra +1 operation in the scalar subquery then begin; v = select min(totalUsed) - 1 from tips; update tips set totalUsed = totalUsed - v; commit; The only difference being whether there is an additional "add" being performed for each row in the original ... If you do an "explain" on the query you see that there is a ONCE instruction (at addr 9) which means to skip the calculation of the scalar after it has been done once (by jumping to location 26 if it has already been calculated once). (The only difference being whether the offset of 1 is done inside the once set, or for each row). sqlite> explain update x set x = x - (select min(x) from x) + 1; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 37000 Start at 37 1 Null 0 1 200 r[1..2]=NULL 2 OpenWrite 0 2 0 1 00 root=2 iDb=0; x 3 Noop 0 0 000 Begin WHERE-loop0: x 4 Rewind 0 35000 5 Noop 0 0 000 Begin WHERE-core 6 Rowid 0 2 000 r[2]=rowid 7 IsNull 2 36000 if r[2]==NULL goto 36 8 Column 0 0 500 r[5]=x.x 9 Once 0 26000 10 Null 0 7 700 r[7..7]=NULL; Init subquery result 11 Integer1 8 000 r[8]=1; LIMIT counter 12 Null 0 9 10 00 r[9..10]=NULL 13 OpenRead 1 2 0 1 00 root=2 iDb=0; x 14 Noop 0 0 000 Begin WHERE-loop0: x 15 Rewind 1 22000 16Noop 0 0 000 Begin WHERE-core 17Column 1 0 11 00 r[11]=x.x 18CollSeq0 0 0 (BINARY) 00 19AggStep0 119 min(1) 01 accum=r[9] step(r[11]) 20Noop 0 0 000 End WHERE-core 21 Next 1 16001 22 Noop 0 0 000 End WHERE-loop0: x 23 AggFinal 9 1 0 min(1) 00 accum=r[9] N=1 24 Copy 9 7 000 r[7]=r[9] 25 DecrJumpZero 8 26000 if (--r[8])==0 goto 26 26 Subtract 7 5 400 r[4]=r[5]-r[7] 27 Add124 300 r[3]=r[12]+r[4] 28 Noop 0 0 000 BEGIN: GenCnstCks(0,1,2,2,0) 29 Noop 0 0 000 END: GenCnstCks(0) 30 Delete 0 682 x 02 31 MakeRecord 3 1 4 D 00 r[4]=mkrec(r[3]) 32 Insert 0 4 2 x 07 intkey=r[2] data=r[4] 33 Noop 0 0 000 End WHERE-core 34Next 0 5 001 35Noop 0 0 000 End WHERE-loop0: x 36Halt 0 0 000 37Transaction0 1 1 0 01 usesStmtJournal=0 38Integer1 12000 r[12]=1 39Goto 0 1 000 sqlite> explain update x set x = x - (select min(x)-1 from x); addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 36000 Start at 36 1 Null 0 1 200 r[1..2]=NULL 2 OpenWrite 0 2 0 1 00 root=2 iDb=0; x 3 Noop 0 0 000 Begin WHERE-loop0: x 4 Rewind 0 34000 5 Noop 0 0 0
Re: [sqlite] Lowering totalUsed
On 12 Jul 2018, at 8:30am, Cecil Westerhof wrote: > I am not quit happy with this. Would it be better to split it in two > queries and feed the result of the first to the second? I would guess that it will run faster. How much faster depends on how many rows there are in the table. Naturally I would say this since I am a fan of multiple short SQL commands rather than one big complicated one. You could enclose the two queries in a transaction if you're worried about something sneaking between them. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lowering totalUsed
2018-07-12 9:30 GMT+02:00 Cecil Westerhof : > A few tables have a not completely apt named column totalUsed. > > It is used to see which records are more used as other records and give > the less used records a bigger chance of being selected. When the numbers > become high I do something like: > UPDATE tips > SET totalUsed = totalUsed - (SELECT MIN(totalUsed) FROM tips) + 1 > > I am not quit happy with this. Would it be better to split it in two > queries and feed the result of the first to the second? > By the way, I wanted to use: UPDATE quotes SET totalUsed = totalUsed - MIN(totalUsed) + 1 but that gives: Error: misuse of aggregate function MIN() -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Lowering totalUsed
A few tables have a not completely apt named column totalUsed. It is used to see which records are more used as other records and give the less used records a bigger chance of being selected. When the numbers become high I do something like: UPDATE tips SET totalUsed = totalUsed - (SELECT MIN(totalUsed) FROM tips) + 1 I am not quit happy with this. Would it be better to split it in two queries and feed the result of the first to the second? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
