[sqlite] How to search column ascii containing chr(0)
Hi All, As subject, I want to know how search column that contain ascii chr(0), I already use google to search but no luck, I'm using python language. Thanks in advance, -iip- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Plan not optimized ?
Hi, Thank you for the answer. Do you know if it's planned ? Do you know a workaround? Regards, Stephane Le samedi 4 juin 2011 12:36:47, Richard Hipp a écrit : 2011/6/3 Stéphane MANKOWSKI steph...@mankowski.fr 4-EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM v_c returns: SCAN TABLE c (~74 rows) EXECUTE CORRELATED SCALAR SUBQUERY 1 SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows) EXECUTE CORRELATED SCALAR SUBQUERY 2 SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows) EXECUTE CORRELATED SCALAR SUBQUERY 3 SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows) For me this is not optimized (but I am not a db specialist) because the computation of total is done 3 times instead of only one. SQLite does not currently implement common subexpression elimination. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to search column ascii containing chr(0)
iip iip.umar.ri...@gmail.com wrote: As subject, I want to know how search column that contain ascii chr(0) select * from MyTable where hex(MyField) like '%00%'; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to search column ascii containing chr(0)
On 6/5/2011 8:47 AM, Igor Tandetnik wrote: iip iip.umar.ri...@gmail.com wrote: As subject, I want to know how search column that contain ascii chr(0) select * from MyTable where hex(MyField) like '%00%'; That query doesn't work. If the field contains 0\n, that would match (300A) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to search column ascii containing chr(0)
On Sun, Jun 5, 2011 at 8:47 PM, Igor Tandetnik itandet...@mvps.org wrote: iip iip.umar.ri...@gmail.com wrote: As subject, I want to know how search column that contain ascii chr(0) select * from MyTable where hex(MyField) like '%00%'; -- Igor Tandetnik yes, I already did that, so there are no other way that more efficient than this one right? because I'm still have to add separator to avoid wrong catch just like '100D' - '10-0D'. Thank a lot, -iip- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to search column ascii containing chr(0)
Robert Myers rob.my...@ziften.com wrote: On 6/5/2011 8:47 AM, Igor Tandetnik wrote: iip iip.umar.ri...@gmail.com wrote: As subject, I want to know how search column that contain ascii chr(0) select * from MyTable where hex(MyField) like '%00%'; That query doesn't work. If the field contains 0\n, that would match (300A) True. Another attempt: select * from MyTable where 2*length(MyField) != length(hex(MyField)); This relies on the fact that built-in function length() seems to stop (erroneously, in my opinion) on the first embedded NUL, while hex() doesn't seem to mind them. At least that's what happens with my copy of SQLite 3.7.2. Beyond this nasty hack, I can't think of anything short of writing a custom function. I tried LIKE and replace() - they don't work with embedded NULs either. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [mlist] How to search column ascii containing chr(0)
iip schrieb: Hi All, As subject, I want to know how search column that contain ascii chr(0), I already use google to search but no luck, I'm using python language. Thanks in advance, -iip- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users What about ... LIKE '%' || X'00' || '%' or even ... LIKE X'250025' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [mlist] How to search column ascii containing chr(0)
res...@googlemail.com wrote: iip schrieb: As subject, I want to know how search column that contain ascii chr(0), I already use google to search but no luck, What about ... LIKE '%' || X'00' || '%' or even ... LIKE X'250025' Doesn't work. That's the first thing I tried. It seems that the LIKE operator treats NUL character as end-of-string, so the test becomes LIKE '%', which of course matches everything. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?
Hi all, Is the way in which SQLite handlesNaN and Infinity values as defined by IEEE-754 documented somewhere? I would also be interested to find a discussion of the rationale behind the design decisions. After some experimenting, it appears that ... * SELECT 1.0 / 0.0 yields NULL (where I would expect to see Inf) * SELECT 1e1 yields an actual IEEE-754 infinity, and it can be stored in a table * SELECT 1e1 + 1e1 yields Infinity, as expected according to IEEE-754. * SELECT 1e1 - 1e1 yields NULL, where I would have expected to see NaN in accordance with IEEE-754. Regards, Sidney ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?
On 5 Jun 2011, at 3:35pm, Sidney Cadot wrote: After some experimenting, it appears that ... * SELECT 1.0 / 0.0 yields NULL (where I would expect to see Inf) * SELECT 1e1 yields an actual IEEE-754 infinity, and it can be stored in a table * SELECT 1e1 + 1e1 yields Infinity, as expected according to IEEE-754. * SELECT 1e1 - 1e1 yields NULL, where I would have expected to see NaN in accordance with IEEE-754. Note that according to SQL semantics, 'NULL' means 'I don't know'. So every value of all types matches with it. Not a useful test. You might learn something by matching with negative or positive underflows, but I have no clue how to express them. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Howto pivot in SQLite
I have a invoice system where one invoice item can have one or more sum items (images). Example is a CD... The invoice item is a CD, there are an infinite numbers of images associated with that CD invoice item. So I have the following: CREATE TABLE Invoice_Item ( Invoice_Item_Id INTEGER PRIMARY KEY AUTOINCREMENT, Invoice_Id INTEGER NOT NULL, Description VARCHAR(80) NOT NULL ) CREATE TABLE Image ( ImageId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Invoice_Item_Id INTEGER , Image_Name VARCHAR(260) NOT NULL, ) The Invoice_Item table has one row in it where the PKID is 1 and description is CD, the Image table has three rows in it, all with a Invoice_Item_Id of 1 and different image names: Img1, Img2, and Img3. In one select statement, I want to return a view of all the Invoice_Items for a particular Invoice such that there is one column that contains all the image names in one string: Invoice_Item_Id | Invoice_Id | Description | Image Names ++-+-- 1 | 1| CD | Img1, Img2, Img3 Can I do this with SQL? If not, can I do this with a user defined function? The UI is going to allow the user to select the row and edit it in another screen. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to search column ascii containing chr(0)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/05/2011 12:20 AM, iip wrote: As subject, I want to know how search column that contain ascii chr(0), I already use google to search but no luck, I'm using python language. If you are using APSW then you can define a user defined function which will work fine: def hasnull(s): return \x00 in s This won't work with pysqlite because it doesn't handle nulls in strings. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3rtsgACgkQmOOfHg372QT5nwCfX0T2jhWWHcHDx9tdd7tZGMVF qfcAoJ8NgSSAno9v3YhIEIzwIpQ9CD5Q =1nqN -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Howto pivot in SQLite
On 5 Jun 2011, at 5:47pm, Sam Carleton wrote: In one select statement, I want to return a view of all the Invoice_Items for a particular Invoice such that there is one column that contains all the image names in one string: Invoice_Item_Id | Invoice_Id | Description | Image Names ++-+-- 1 | 1| CD | Img1, Img2, Img3 Take a look at the group_concat() function: http://www.sqlite.org/lang_aggfunc.html So just like you can use max(X), or total(X) in a SELECT, you can use group_concat(Image_Name) to string a bunch of returned values together, maybe something like SELECT group_concat(Image_Name) FROM Image WHERE Invoice_Item_Id = 1 Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?
Note that according to SQL semantics, 'NULL' means 'I don't know'. I am not quite sure what you are saying. I am pretty sure that NULL is not defined so informally ... :) So every value of all types matches with it. I don't understand what matches with means in this context, sorry. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?
On 5 Jun 2011, at 6:10pm, Sidney Cadot wrote: Note that according to SQL semantics, 'NULL' means 'I don't know'. I am not quite sure what you are saying. I am pretty sure that NULL is not defined so informally ... :) I expressed it in a short way, but I think it's a fair summary. Take a look here: http://en.wikipedia.org/wiki/Null_(SQL) Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database. So every value of all types matches with it. I don't understand what matches with means in this context, sorry. If you compare anything with NULL, you will get NULL as a result, even if the thing you're comparing isn't NULL. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
Matthew L. Creech mlcre...@gmail.com писал(а) в своём письме Sat, 04 Jun 2011 02:26:09 +0600: Coincidentally, I happened to be reading over this page just earlier today: http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 Obviously a bit biased toward PostgreSQL (since it's hosted there), but it points out some fairly specific differences in features, performance, etc. (I have no personal experience either way, but was just curious myself). Posting stuff like that on your site is pathetic :( If you build a nice DBMS, you should work on how to make it even better, not on how to slander different DBMSes. I work with MySQL for years, both in small and medium-loaded applications, and it works like a charm for me. Extremely fast, extremely reliable, full of nice features like native fulltext support. If you need a non-transactional (atomic), very fast, reliable and simple server-like DBMS, then MySQL+MyISAM would suit you perfectly. If you need transactions (although in most of the web cases you don't need them), you can use InnoDB. And if your application is heavy, then I guess Oracle would be a better choice for you. After reading this article, I'm starting to disrespect Postgre :( The article is very questionable in places. Yes, SQLite documentation also has comparisons against MySQL, but it never offends it. Regards, Serge ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Howto pivot in SQLite
On Sun, Jun 5, 2011 at 1:04 PM, Simon Slavin slav...@bigfraud.org wrote: Take a look at the group_concat() function: http://www.sqlite.org/lang_aggfunc.html That is PERFECT, thank you! If the person who thought of this function originally is reading this, thank you!!! What a time saver! Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
On 2011-06-05 12:26, Dagdamor wrote: If you need a non-transactional (atomic)... If you need transactions (although in most of the web cases you don't need them)... Non-transactional is by definition not atomic. With the single exception of something that is strictly read-only, I have never, ever, seen any database application that did not need transactions. Ever. There's more to transactions than just grouping together multiple SQL statements. If you do any INSERTs, UPDATEs, or DELETEs at all, you need transactions, even if there are no indexes involved, even if the individual statements don't affect each other. Even a simple insert can involve multiple operations like requesting more disk space from the OS, rearranging pointers in the database file on the disk, etc. What happens if there's a power failure while it's in the middle of that insert? With transactions, it gets rolled back when things start up again. Without, you could wind up with a corrupted database. Joe D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Howto pivot in SQLite
On Sun, Jun 05, 2011 at 12:47:47PM -0400, Sam Carleton scratched on the wall: In one select statement, I want to return a view of all the Invoice_Items for a particular Invoice such that there is one column that contains all the image names in one string: Invoice_Item_Id | Invoice_Id | Description | Image Names ++-+-- 1 | 1| CD | Img1, Img2, Img3 Can I do this with SQL? As others have pointed out, you can, but that doesn't always make it a good idea. If you're doing this just to turn around and split that value back up in your application code, you might want to rethink your data handling. The data representation in the database is clear and correct. Do you really want to alter that representation, smashing the image names together into a single, less clear value, just for the sake of making one query, rather than two? Or even one query, but with an extra line or two of code in the parse function? Why not just deal with values in their native, and more correct, list of images format? -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Howto pivot in SQLite
On Sun, Jun 5, 2011 at 5:44 PM, Jay A. Kreibich j...@kreibi.ch wrote: On Sun, Jun 05, 2011 at 12:47:47PM -0400, Sam Carleton scratched on the wall: In one select statement, I want to return a view of all the Invoice_Items for a particular Invoice such that there is one column that contains all the image names in one string: Invoice_Item_Id | Invoice_Id | Description | Image Names ++-+-- 1 | 1| CD | Img1, Img2, Img3 Can I do this with SQL? As others have pointed out, you can, but that doesn't always make it a good idea. If you're doing this just to turn around and split that value back up in your application code, you might want to rethink your data handling. The data representation in the database is clear and correct. Do you really want to alter that representation, smashing the image names together into a single, less clear value, just for the sake of making one query, rather than two? Or even one query, but with an extra line or two of code in the parse function? Why not just deal with values in their native, and more correct, list of images format? Jay, There is one simple reason: Time This is for my evening/weekend business where time is precious, I have been dragging my feet on the current feature simply because I could not get my head around how best to implement it. It dawned on me today to take this REALLY simple and less the idea approach for the general display of the invoice and then to allow the user to select the line and bring up a secondary dialog to manage the list of images. It is quick and to the point. Once I get this in my customers heads, I will get feedback from them for better ideas. The bottom line is I am trying to NOT over engineer things and let my customers drive things. It seems to work well, they love seeing their feedback taken to heart and I love their input:) For now it is a good start:) Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
Darren Duncan wrote: MySQL should be avoided like the plague. I hereby retract my above-quoted statement as I realize that it is too severe a statement to be making. Instead I will say the following in its place: MySQL should not be considered as the default choice of a non-lite SQL DBMS, for projects not currently using it, when you have a choice between multiple SQL DBMSs; instead, the default non-lite choice should be Postgres. If you don't know the difference, then Postgres will serve you much better and keep you safer. Just choose MySQL if you are informed enough about various SQL DBMSs and can thereby justify that MySQL will actually serve your needs better. One reason for this is that Postgres defaults to more safer behaviors, while with MySQL you have to explicitly ask for some of the same safety nets, and people less knowledgeable about it won't know to do this. I will also say that for business-level use, it is no justification to say that MySQL is your only choice because that is the only thing the web host provides. If you're a serious business user, you have a lot more leverage to pick and choose any choice of software you want. Use Postgres instead if you have to switch to a larger SQL DBMS. But hopefully the help you've gotten so far will extend your mileage with SQLite and you won't have to switch to anything yet. See also my prior reply, about CHECK having never been fixed/supported in MySQL. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
Joe D j...@cws.org писал(а) в своём письме Mon, 06 Jun 2011 00:57:51 +0600: Non-transactional is by definition not atomic. With the single exception of something that is strictly read-only, I have never, ever, seen any database application that did not need transactions. Ever. There's more to transactions than just grouping together multiple SQL statements. If you do any INSERTs, UPDATEs, or DELETEs at all, you need transactions, even if there are no indexes involved, even if the individual statements don't affect each other. Even a simple insert can involve multiple operations like requesting more disk space from the OS, rearranging pointers in the database file on the disk, etc. What happens if there's a power failure while it's in the middle of that insert? With transactions, it gets rolled back when things start up again. Without, you could wind up with a corrupted database. You are mixing two different issues: atomic operations and automatic repair after crash. MyISAM tables *are* atomic. Even if you update 1000 rows at once, another process will never see a moment when only 500 of them are updated and another 500 are not. About repair, yes, MySQL is not as much concerned about that, and it never became a problem to me either (my servers don't crash every day :)). See, if your server died in the middle of transaction, the best thing even a transactional DBMS can do it to roll it back. *But you will lose that data anyway*, despite that the table would be not corrupted, and usually, data loss is more serious issue rather than a need to run table check for several seconds. MyISAM tables would never become so popular if automatic rollback after crash was a serious issue for server owners. Regards, Serge ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
Darren Duncan dar...@darrenduncan.net писал(а) в своём письме Mon, 06 Jun 2011 05:08:45 +0600: MySQL should not be considered as the default choice of a non-lite SQL DBMS, for projects not currently using it, when you have a choice between multiple SQL DBMSs; instead, the default non-lite choice should be Postgres. Wow, communistic regime is back! Thanks for telling me what I should do and what not, what I should use and what not. ;) Mind you, once Postgre will become more or less known world-wide (its usage is not comparable with either MySQL or SQLite which are way more popular), you will have exactly the same issues: lots of bugs open, lots of features missing, lots of holes in SQL compliance found. The fact that all that wide field is not discovered yet, doesn't make Postgre the best. Regards, Serge ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite shell text wrapping?
After creating a Database.sqlite i then create a Table and try to past text from a text file into the shell, but the text wraps? Im working on an iphone app and im new to it all. Ive been reading lots of tutorials and it seems that everyone builds databases this way. I have just under 4,000 lines of code i pre-typed in a text file. I want to be able to copy and paste it all into the shell. Example of the text I'm Pasting into Shell:INSERT INTO Datalist(staff,floor) VALUES(‘Dick, Tom’,2); Example of the text after Pasting into Shell:INSERT INTO Datalist(staff,floor) Dick, ,2);TomVALUES( Please someone help whats the best way to get all this info in a .sqlite database by copy past. Also if there is a good tutorial out there could i please get the link. Thank you Kyle ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
Dagdamor wrote: Darren Duncan dar...@darrenduncan.net писал(а) в своём письме Mon, 06 Jun 2011 05:08:45 +0600: MySQL should not be considered as the default choice of a non-lite SQL DBMS, for projects not currently using it, when you have a choice between multiple SQL DBMSs; instead, the default non-lite choice should be Postgres. Wow, communistic regime is back! Thanks for telling me what I should do and what not, what I should use and what not. ;) What I'm saying is analogous to saying people should default to wearing helmets when riding bikes and only not wear helmets on bikes when they can justify it. You'll still get where you're going either way, but one way is the typically safer one. Mind you, once Postgre will become more or less known world-wide (its usage is not comparable with either MySQL or SQLite which are way more popular), you will have exactly the same issues: lots of bugs open, lots of features missing, lots of holes in SQL compliance found. The fact that all that wide field is not discovered yet, doesn't make Postgre the best. Postgres is quite widely used already, though not as widely as MySQL, so it gets a big workout and exposure of bugs. One big reason I recommend Postgres as a first choice now is that I have some familiarity with the community that makes it. The Postgres makers take quality and reliability as top concerns, and have for a long time, so to make the product much more solid. They have high standards for declaring the DBMS production ready and lengthy testing/shakeout periods. Despite this, Postgres still releases a major version about once per year, where each version goes through alpha/beta/RC/etc stages on a semi-predictable schedule. And then after it is released, a major version is only updated minimally, to fix security or other bugs that become known, so users can be confident that minor version updates are just going to be more solid and not risk breaks due to larger changes. New features or non-bugfix changes only come out in the yearly major versions. I don't believe that MySQL development has anywhere near this kind of rigor. See also the Change logs for both products with each minor release and just what kinds of bugs each one is fixing, including their severity. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Howto pivot in SQLite
On 06/06/2011, at 8:30 AM, Sam Carleton scarle...@miltonstreet.com wrote: allow the user to select the line and bring up a secondary dialog to manage the list of images You could simply execute a second select when the user asks for the set of images for that invoice. It's simpler and more accurate to then iterate through the returned rows than to parse a comma separated string. Tom BareFeetWare ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
On 6 Jun 2011, at 12:20am, Dagdamor wrote: Darren Duncan dar...@darrenduncan.net писал(а) в своём письме Mon, 06 Jun 2011 05:08:45 +0600: MySQL should not be considered as the default choice of a non-lite SQL DBMS, for projects not currently using it, when you have a choice between multiple SQL DBMSs; instead, the default non-lite choice should be Postgres. Wow, communistic regime is back! Thanks for telling me what I should do and what not, what I should use and what not. ;) Dude, it's just advice. That's all any of us do: post our opinions. Calm down. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite shell text wrapping?
On 6 Jun 2011, at 12:41am, Kyle Malloy wrote: After creating a Database.sqlite i then create a Table and try to past text from a text file into the shell, but the text wraps? Im working on an iphone app and im new to it all. Kyle, first, do not use directional quotes in SQLite. Use the apostrophe character around strings 'Dick, Tom' don't do what is in your message like this ‘Dick, Tom’ Second, if you have lots of data in a tab-delimited format, you can read it into a database using the SQLite command-line tool. Download it from here: http://www.sqlite.org/download.html Documentation is here: http://www.sqlite.org/sqlite.html You probably want something like sqlite3 Database.sqlite sqlite CREATE TABLE myTable ( whatever ); sqlite .separator \t sqlite .import myFile.csv myTable Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite shell text wrapping?
On 06/06/2011, at 9:41 AM, Kyle Malloy wrote:
After creating a Database.sqlite i then create a Table and try to past text
from a text file into the shell, but the text wraps? Im working on an iphone
app and im new to it all. Ive been reading lots of tutorials and it seems
that everyone builds databases this way. I have just under 4,000 lines of
code i pre-typed in a text file. I want to be able to copy and paste it all
into the shell.
Example of the text I'm Pasting into Shell:INSERT INTO Datalist(staff,floor)
VALUES(‘Dick, Tom’,2);
Example of the text after Pasting into Shell:INSERT INTO
Datalist(staff,floor) Dick, ,2);TomVALUES(
Please someone help whats the best way to get all this info in a .sqlite
database by copy past. Also if there is a good tutorial out there could i
please get the link. Thank you Kyle
You should:
1. Use straight quotes, not smart quotes. (But I suspect they're only appearing
here in your mail message, not your importing file.)
2. Normalize your data. Don't put multiple values (eg Tom Dick) in one
column. Instead, create a people table and another table that links multiple
people with each floor.
Something like this:
create table staff
( id integer primary key not null
, first name text collate nocase
, last name text collate nocase
, email text collate nocase
)
;
create table Floor
( id integer primary key not null
, some other info, unique to each floor text
)
;
create table Floor Staff
( id integer primary key not null
, floor integer not null references Floor(id)
, staff integer not null references Staff(id)
)
;
-- Then insert your data:
begin
;
insert into Staff (first name) values ('Tom')
;
insert into Staff (first name) values ('Dick')
;
insert into Floor Staff (floor, staff)
select 2, id from Staff where first name = 'Tom'
;
insert into Floor Staff (floor, staff)
select 2, id from Staff where first name = 'Dick'
;
commit
;
If you want to get a list of people on a particular floor, just select like
this:
select first name
from Floor Staff join Staff on Floor Staff.staff = Staff.id
where Floor = 2
;
-- or as a comma separated list:
select group_concat(first name, ', ')
from Floor Staff join Staff on Floor Staff.staff = Staff.id
where floor = 2
;
Tom
BareFeetWare
--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?
Hi Jay, However, it is worth remembering that IEEE 754 is really about building processors, not about end-user interaction. While it is a rigid, formal specification of a numeric environment, at its heart it is about mechanics, not about consistent mathematical systems built on theorems and proofs. While it is a bit off-topic, I disagree with this assessment. The 754 standard has been carefully crafted to allow rigorous statements about the stability of numerical algorithms. It chooses deliberately to sacrifice easy hardware implementation in favor of well-defined semantics. In fact, it took quite some time before compliant hardware implementations were available after its inception. As others have pointed out, one of the meanings of NULL is essentially unknown. Yes, but in terms of IEEE-754, there exist no unknown results. Any of the elementary operations (+ - * /) has a fully bitwise predictable result (subject to the rounding mode). * SELECT 1.0 / 0.0 yields NULL (where I would expect to see Inf) NULL is returned any time 0 or 0.0 is seen on the right side of a divide expression. Is that behavior mandated behavior by any of the SQL standards, or is that an implementation choice by SQLite? Given NULL to mean unknown, this makes a lot more sense. This is a prime example of the difference between IEEE 754 and a real world numeric environment. Anyone that knows a bit of math isn't going to expect 2.0/0.0 and 1.0/0.0 to yield different answers. In IEEE-754, they don't. Both return +infinity. You may be thinking of 0.0 / 0.0, which does return NaN in IEEE-754. For what it is worth: I know a bit of math, and I actually expect floating point operations to follow the IEEE-754 mandated behavior nowadays. Unless the SQL standard mandates specific behavior, of course; in the context of SQLite, that would clearly take precedence over IEEE-754. Unfortunately I do not know any of the SQL standards nearly as well as IEEE-754. As a matter of principle, I think it is not good practice to give this is what people expect in the real world precedence over a well-defined, rigorous standard. For one thing, it is quite debatable what people expect in the real world. For another thing, rather smart people have thought real hard to make IEEE-754 semantically consistent; overriding such deliberations with gut feelings about how things should behave doesn't sound like a good idea to me. But NULL makes a lot of sense in the proscribed environment, and is much more consistent with the rest of SQL's operators. That is a respectable position to take on the issue. However, it limits the usefulness of SQLite for storing scientific data (which is what I am trying to use it for, currently). Here, there is a clear and useful distinction between a missing value and a NaN -- the latter meaning a calculation was done but it failed in any of the standard-prescribed ways. The difference is subtle but real. Many, probably most, languages used for scientific computation distinguish between those two concepts. Lastly: if SQLite chooses to unify the floating-point concept of NaN and the SQL concept of NULL, I feel it should do the same for +/- infinity. As it stands, it seems to incorporate one concept of IEEE-754 (+/- infinity) while omitting the NaN. My personal feeling is that a clear choice should be made. But more importantly: the behavior needs to be documented. The SQLite docs are silent on the issue of floating point behavior, as far as I can tell. Sidney ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?
Hi Simon, Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database. To me, this statement does not apply to NaN, which is a perfectly fine (albeit unusual) floating point value. If you compare anything with NULL, you will get NULL as a result, even if the thing you're comparing isn't NULL. This depends on how you compare: SELECT NULL = NULL; -- NULL SELECT NULL IS NULL; -- 1. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?
On 6 Jun 2011, at 3:02am, Sidney Cadot wrote: Hi Jay, As others have pointed out, one of the meanings of NULL is essentially unknown. Yes, but in terms of IEEE-754, there exist no unknown results Jay is talking about SQL. SQL /does/ use NULL for 'unknown'. And the OP was trying to match values with NULL. And was using a SQL command to do it. So the matching will be done using SQL semantics. See this page for the result: http://www.sqlite.org/nulls.html If you want to explicitly test for NULL, use the function typeof(). Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?
Hi Simon,
Jay is talking about SQL. SQL /does/ use NULL for 'unknown'.
Well yes, it does, but my entire point is that floating point NaN is
quite different from Unknown.
SQLite sort-of unifies NaN and NULL (although this isn't documented).
However, this is not an SQL choice -- it is an implementation choice.
PostgreSQL and MySQL, for example, handle NaN values as floating point
values, quite distinctly from NULL values, e.g. in Postgres:
# SELECT CAST('NaN' AS DOUBLE PRECISION);
float8
NaN
(1 row)
And the OP was trying to match values with NULL.
Actually, no, I was asking how SQlite behaves with respect to IEEE-754
floating point.
Sidney
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?
On 6 Jun 2011, at 3:38am, Sidney Cadot wrote: And the OP was trying to match values with NULL. Actually, no, I was asking how SQlite behaves with respect to IEEE-754 floating point. But you were using a SQL command to make the match. Here it one of them: On 5 Jun 2011, at 3:35pm, Sidney Cadot wrote: After some experimenting, it appears that ... * SELECT 1.0 / 0.0 yields NULL (where I would expect to see Inf) You executed a SELECT command and got an answer from SQL. That answer does not mean The result of the calculation '1.0 / 0.0' is the value NULL. which is what it would mean if you did the maths yourself. What it means from SQL is The result of '1.0 / 0.0' is unknown or missing. because you asked SQL, not a maths library. In this case, it's unknown. Because SQLite doesn't know how to do that calculation. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?
Hi Simon, But you were using a SQL command to make the match. Well, I was using it to demonstrate some behavior I observed, yes. I was not matching values with NULL. But whatever. You executed a SELECT command and got an answer from SQL. That answer does not mean The result of the calculation '1.0 / 0.0' is the value NULL. That's a bit of a philosophical point. In the context of SQLite, the expression 1.0 / 0.0 evaluates to something that is representable, namely NULL, that much is clear. Whether NULL is to be considered a value is up for debate. It is a bit weird to be able to represent that what cannot be represented, so I'd take the IMHO simpler interpretation of just accepting NULL as a value of any non NOT NULL domain. because you asked SQL, not a maths library. In this case, it's unknown. Because SQLite doesn't know how to do that calculation. Accepting that, my question becomes: why does SQLite elect to not know what to do? The handling of NaN results is special cased in the code (see http://www.sqlite.org/cvstrac/chngview?cn=5066). Why not just accept the existence of NaN as a valid floating point value? Postgres, Mysql, and (from a quick google) Oracle and DB2 do this. SQLite is really the odd one out, here. Sidney ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
