Re: [sqlite] Logging only SQL commands which make changes, parsing PRAGMAs
2012/8/23 Simon Slavin slav...@bigfraud.org: I'm trying to log SQL commands which might make changes to the database, but not those which just read it... Simon. Use triggers. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert two select count(*) problem
2012/8/9 YAN HONG YE yanhong...@mpsa.com: The sqlcmd is: insert into mydb (co1,co2) select count(*) from db1 where db1.sco180 ,select count(*) from db2 where db2.sco290; It doesn't work. If one column, it can do,but when many column count(*), I don't know how to write the sql command. INSERT INTO mydb (co1, co2) SELECT (SELECT count(*) FROM db1 WHERE sco180), (SELECT count(*) FROM db2 WHERE sco290); -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 56, Issue 5
2012/8/6 YAN HONG YE yanhong...@mpsa.com: I wanna insert into a value to my sqlte db: insert into mydb (number1) select count(number1) from db2 where number180; but it doesn't work sqlite CREATE TABLE db2(number1 int, name text); sqlite INSERT INTO db2 VALUES (20,'Name 2'); sqlite INSERT INTO db2 VALUES (90,'Name 9'); sqlite INSERT INTO db2 VALUES (140,'Name 14'); sqlite CREATE TABLE mydb(number1 int); sqlite INSERT INTO mydb (number1) SELECT count(number1) FROM db2 WHERE number180; sqlite SELECT * FROM mydb; 2 -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to insert UTF-8 strings in SQLITE3.EXE?
2012/5/13, Frank Chang frank_chan...@hotmail.com: Here is another way I found out how insert UTF-8 strings in SQLITE3.EXE. F:\sqlite3_6_16sqlite3.exe mdName.dat SQLite version 3.6.16 Enter .help for instructions Enter SQL statements terminated with a ; sqlite INSERT INTO PREFIX SELECT CAST(x'52C3B373' AS TEXT),'M','Ros','10'; sqlite .quit sqlite CREATE TABLE PREFIX (a TEXT, b TEXT, c TEXT, d INT); sqlite INSERT INTO PREFIX SELECT CAST(x'52C3B373' AS TEXT),'M','Ros','10'; sqlite INSERT INTO PREFIX SELECT x'52C3B373','M','Ros','10'; sqlite INSERT INTO PREFIX VALUES (x'52C3B373','M','Ros','10'); sqlite SELECT * FROM PREFIX; Rós|M|Ros|10 Rós|M|Ros|10 Rós|M|Ros|10 -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE '' returns value
2012/5/9 Petite Abeille petite.abei...@gmail.com: On May 9, 2012, at 8:32 PM, Black, Michael (IS) wrote: That doesn't appear to work for 2.8.17. 2.8.17? Current as of December 19, 2005?!? Oh, well... :D http://www.sqlite.org/changes.html#version_2_8_17 $ sqlite -version 2.8.17 $ sqlite3 -version 3.6.23 openSUSE 11.3 -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE '' returns value
2012/5/9 Petite Abeille petite.abei...@gmail.com: On May 9, 2012, at 9:21 PM, Kit wrote: $ sqlite -version 2.8.17 Perhaps the OP got confused between sqlite and sqlite3? :D $ sqlite3 -version 3.6.23 Better, but still over two years old, March 9, 2010: I have installed both versions. http://www.sqlite.org/changes.html#version_3_6_23 openSUSE 11.3 According to the intraweb, openSUSE 11.3 was released on July 15, 2010. Are you suggesting the OP should perhaps consider upgrading to something more, hmm, contemporary? Why? My upgrade cycle is 2-3 years. For your pleasure - another computer installed 1 week ago: $ sqlite -version 2.8.17 $ sqlite3 -version 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e Ubuntu 12.04 Precise Pangolin LTS Version of SQLite2 is still 2.8.17. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG: No reliable way to import strings from a file
2012/4/21 Braddock Gaskill bradd...@braddock.com:
Hi Guys,
This may be a known issue. I know sqlite isn't known for it's CSV
support.
But it makes CSV .import almost useless for string types, even if you know
there will be no embedded commas or other delimiters.
There is NO SAFE WAY to import string fields using sqlite's .import
function.
If the string can be interpreted as an numeric value, even if it
overflows, it will be turned into an Inf.
This is true even when the column type is specified as a string.
sqlite3 --version
3.6.22
cat EOF data.tab
Hello World!
1234e12345
EOF
sqlite3
sqlite CREATE TABLE foo (myfield STRING);
sqlite .import data.tab foo
sqlite select * from foo;
Hello World!
Inf
sqlite
Thanks, I love Sqlite!
-braddock
sqlite CREATE TABLE foo (myfield STRING);
sqlite INSERT INTO foo VALUES ('255e2555');
sqlite SELECT * FROM foo;
Inf
sqlite DROP TABLE foo;
sqlite CREATE TABLE foo (myfield TEXT);
sqlite INSERT INTO foo VALUES ('255e2555');
sqlite SELECT * FROM foo;
255e2555
sqlite CREATE TABLE foo (myfield VARCHAR);
sqlite INSERT INTO foo VALUES ('255e2555');
sqlite SELECT * FROM foo;
255e2555
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: database is locked on redhat6
2012/4/21 田晶 tianj...@genomics.cn: Hi all, I have a question when using sqlite on redhat6, I put the sqlite file on nfs storage(this storage is shared by isilion), the client using redhat 6 x64. Tianjing Never use SQLite on NFS storage. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how much case...when... command?
2012/4/17 YAN HONG YE yanhong...@mpsa.com: UPDATE bb SET Slevel = CASE price112 WHEN 1 THEN 1 ELSE 0 END + CASE price130 WHEN 1 THEN 1 ELSE 0 END + CASE price220 WHEN 1 THEN 1 ELSE 0 END + CASE price230 WHEN 1 THEN 1 ELSE 0 END + case... csse... ... CASE price280 WHEN 1 THEN 1 ELSE 0 END; in this command, I don't know sqlite allow how much case...when... in one sql command. The maximum number of bytes in the text of an SQL statement is limited to SQLITE_MAX_SQL_LENGTH which defaults to 100. You can redefine this limit to be as large as the smaller of SQLITE_MAX_LENGTH and 1073741824. http://www.sqlite.org/limits.html -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] auto-incrementing integer in composite primary key
2012/4/16 Puneet Kishor punk.k...@gmail.com: I am experimenting with a home-grown versioning system where every significant modification to row would be performed on a copy of the row, the original being preserved. Any other suggestions to achieve a similar functionality would be welcome. -- Puneet Kishor 1. Use Git or Mercurial 2. Try this: CREATE TABLE instance ( filename TEXT, version INT, size INT, md5sum TEXT, creation_date TEXT, last_write_time TEXT, PRIMARY KEY(filename,version), FOREIGN KEY (md5sum) REFERENCES resource (md5sum) ); CREATE TABLE resource ( md5sum TEXT, data BLOB, primary key(md5sum) ); -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] auto-incrementing integer in composite primary key
2012/4/16 Puneet Kishor punk.k...@gmail.com: I am trying to create a data versioning system so that a query done at a particular time can be reproduced identically as to the original query even if the data have been modified in the interim time. CREATE TABLE doc ( id INTEGER PRIMARY KEY autoincrement, record TEXT ); CREATE TABLE t ( id INTEGER PRIMARY KEY autoincrement, doc_id INTEGER, rec TEXT, created_on DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(doc_id) REFERENCES doc(id) ); SELECT doc.record, t.rec FROM doc LEFT JOIN t ON doc.id=t.doc_id WHERE doc.id=id_xx AND created_on=time_xx ORDER BY created_on DESC LIMIT 1; `id_xx` and `time_xx` are keys for search. You may use some additional indexes. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] auto-incrementing integer in composite primary key
2012/4/16 Petite Abeille petite.abei...@gmail.com: On Apr 16, 2012, at 9:09 PM, Kit wrote: SELECT doc.record, t.rec FROM doc LEFT JOIN t ON doc.id=t.doc_id WHERE doc.id=id_xx AND created_on=time_xx ORDER BY created_on DESC LIMIT 1; - how do you represent deleted rows? I will create a new record with attribute deleted and new timestamp. - how do you avoid version ambiguities (e.g. two rows created with the same timestamp)? UNIQUE index on (t.doc_id,t.created_on) A modified select: SELECT doc.record, t.rec, t.created_on FROM doc LEFT JOIN t ON doc.id=t.doc_id WHERE doc.id=id_xx AND created_ontime_xx GROUP BY t.doc_id HAVING created_on=max(created_on); -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why are two select statements 2000 times faster than one?
2012/4/15 Steinar Midtskogen stei...@latinitas.org: So, is there really a way to create an index in a virtual table, or a way to emulate this? Why? You don't need this. Use index on base tables. My xRowid function simply returns the value of the unix_time column, but even select max(rowid) is equally slow. Steinar Why you need select max(rowid)? Something is wrong in your data design. Use autoincrement. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How should I get the affected rows' ids
UPDATE this_table SET data='abc' WHERE id 2; SELECT id FROM this_table WHERE id 2; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating virtual tables
Create next table with columns min, max, count and create triggers for insert, delete and modification. -- Kit 2012/3/20, Rita rmorgan...@gmail.com: Hello, I have a single table which has close to 4 millions rows. I write once and read many times with SELECT. I mainly work with operations like max, min, and count so I was wondering instead of creating views is there a way I can get the count() of a table and place it in a SQL variable or table? While the table is getting populated I would like to build this counter. Or have a SQL variable (if possible) like, count=select count(id) from table; -- --- Get your facts first, then you can distort them as you please.-- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there any option that can enable me to do INSERT or UPDATE while SELECT
2012/3/14, FengTao DING hwk...@motorola.com: Because in my UPDATE case, the new values that will be updated come from another different table, and the UPDATE's WHERE condition also involves columns from more than one tables(actually 3), UPDATE tab1 SET val1=(SELECT val2 FROM tab2 WHERE ...) WHERE val3 in (SELECT val4 FROM tab4 JOIN tab5 ON ... WHERE ...); -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/12, Christoph P.U. Kukulies k...@kukulies.org: INSERT OR IGNORE INTO resource ... INSERT OR REPLACE INTO instance ... Thanks a lot. What is the idea behind the INSERT OR REPLACE in your solution? Christoph If you edit a test for some version of your software, md5sum is changed too. You must replace row in `instance`. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/12 Christoph P.U. Kukulies k...@kukulies.org: Wouldn't the second and the third run result in being the records just replaced (since they have the same data in all columns and I don't have any uniqueness defined). But instead I have three identical entries from each run. Christoph I recommend to add an attribute `version`. Version of main application, not test. Add primary key or unique. CREATE TABLE instance ( path TEXT, basename TEXT, version TEXT, size INT, md5sum TEXT, creation_date TEXT, last_write_time TEXT, FOREIGN KEY (md5sum) REFERENCES resource (md5sum), PRIMARY KEY (path,basename,version) ); You may try s/PRIMARY KEY/UNIQUE/ -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/9 Christoph P.U. Kukulies k...@kukulies.org: CREATE TABLE instance ( path TEXT, basename TEXT, size INT, md5sum TEXT, creation_date TEXT, last_write_time TEXT, FOREIGN KEY (md5sum) REFERENCES resource (md5sum) ); CREATE TABLE resource ( md5sum TEXT, data BLOB, primary key(md5sum) ); What makes the contents of two files equal (so that their contents can be represented by the same resource) ? md5sum = md5sum My problem: what do I have to change in TABLE instance so that I can use it to determine whether the key is already in the resource TABLE? - Make new md5sum from new data INSERT OR IGNORE INTO resource ... INSERT OR REPLACE INTO instance ... Would that be a FOREIGN KEY? And how would I do that in syntax? If I need FOREIGN KEY, would I have to enable that in SQLite somehow (at compile time)? Thanks, Christoph PRAGMA foreign_keys = ON; in runtime. But you don't need foreign keys support in this case. You may use it for garbage collection. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What do people think of SQLite Root?
Announcement of the release Sqlite Root http://www.sqliteroot.com/ now available for Linux. Any feedback is appreciated. Fabio Spadaro Two big problems: - license - size This software is unusable for me. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE failure
2012/3/6 a.azzol...@custom.it: UPDATE table_name SET IdDeptGroup=1, Gross=Gross+ integer_value WHERE Id_= Id_value Have you ever seen a mismatch(sum is lower then expected) in the final sum result ? Missing some `Id_` in the table? -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/4 Christoph P.U. Kukulies k...@kukulies.org: Thanks for the ideas. The problem is with md5sum clashes: all files with 0 bytes have the same md5sum. Also files with same contents have the same md5sum but may have a different name. That's no problem. if you put names to another table, you may share one content. Normalize. One cell, one atomic information. You may use md5sum as text primary key or you may define PRIMARY KEY(filename,md5sum,size). Interesting. How would that work syntaxwise? I mean, at table creation time? CREATE TABLE dir(filename text, md5sum text, size int, content blob, PRIMARY KEY(filename,md5sum,size)); Filename, version, date and size put to another table with md5sum as a foreign key. Inspire with Git system or use it. I will have a look at Git. Christoph If you planning version control system, you may select from completed systems, eg. Subversion, Git, Mercurial and Bazaar. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/4 Christoph P.U. Kukulies k...@kukulies.org: At the moment I have everything in the filesystem. The test situation is that about 500 testprograms have to be run with product release N against product release N-1. The test programs are duplicated at the moment in all places. The deployments of product release builds N, N-1, N-2 are also in the filesystem. You may use Git with branches for different versions If I boil it all down from, say 60 GB to 2 GB (as my first attempts resulted in), I could generate the testbed on the push of a button out of the database and assemble it in a tree with two branches, the new candidate branch and the reference branch, run the tests. That all could be web app based in the end. Git use own database solution optimized for performance and allows modify all glue scripts in userspace. You may personalize Git for different purposes. Just a question on NORMALIZATION: Is normalization being done on a database by some operation, like giving a command: normalize it or is it done by design of the data structures being entered? Christoph Normalization is manually process to minimize redundancy and dependency from data structures. See Database normalization. You may normalize your table to 3 tables and denormalize to 2 tables. Replace attribute size with attribute version. Primary key first table will be reduced to (filename,version), primary key second table to (md5sum). -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/4 Christoph P.U. Kukulies k...@kukulies.org: CREATE TABLE dir(filename text, md5sum text, size int, content blob, PRIMARY KEY(filename,md5sum,size)); Coming back to your suggestion using PRIMARY KEY(filename,md5sum,size), how would I address this PRIMARY KEY, example: I'm building a table CREATE TABLE candidate (?,client TEXT, md5sum TEXT, basename TEXT, size INT, ...some other stuff ) The ? should stand for the - is that FOREIGN KEY? - key in the dir-TABLE which is formed of filename,md5sum,size. Christoph Foreign key is (basename,md5sum,size). Your example shows that a composite key in this case is possible, but it is not appropriate. Use simple key md5sum. Make two tables. Attribute size (part of primary key) is redundant. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/3 Christoph P.U. Kukulies k...@kukulies.org: I'm building a unique ID made up from the basename, md5sum and size of the file. This results as a TEXT PRIMARY KEY (e.g. filename_md5sum_size). Can I use (I'm using System.Data.SQLite) a try clause to find out whether the entry is already there? I think this can be faster than asking the database by an SQL statement, whether the KEY is already there. Christoph Normalize. One cell, one atomic information. You may use md5sum as text primary key or you may define PRIMARY KEY(filename,md5sum,size). Filename, version, date and size put to another table with md5sum as a foreign key. Inspire with Git system or use it. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/2, Christoph P.U. Kukulies k...@kukulies.org: When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), Yes. would this imply uniqueness? Yes. Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? Christoph Kukulies No. PRIMARY KEY is always UNIQUE. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get schema from attached database ?
2012/2/26 黃楨民 huangchen...@gmail.com: Dear all: I run sqlite3 In windows shell command environment and make a connection to database in thw way like 『sqlite3 dbone.sq3』. Then, attach database 'dbtwo.sq3' as dbtwo. How could I get schema of detwo under windows shell command ? Please help! Best Regards tom SELECT sql FROM dbtwo.sqlite_master WHERE type='table'; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] one table versus multiple
2012/2/24 Rita rmorgan...@gmail.com: I was wondering if there is a better way to normalize this data. And also, I will be doing queries like, what is the most recent price for the user,mson, and what is the average price for mson in the last 3 days, etc... Your table is already normalized. The primary key is t+user, attribute price is dependent on the entire primary key. No dependency between attributes or between parts of the primary key. However, a problem might occur if the user at the same time wants to make 2 records. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to check whether a table is empty or not in sqlite.
2012/2/13 Igor Tandetnik itandet...@mvps.org: bhaskarReddy uni...@gmail.com wrote: How to check whether a table is empty or not. If a table is empty, i want to do some logic. If not another logic. select exists (select 1 from MyTable); SELECT exists(SELECT 1 FROM MyTable LIMIT 1); -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Random number generator
2012/2/11 Igor Tandetnik itandet...@mvps.org: On 2/10/2012 10:52 PM, Rick Guizawa wrote: Hi All, how do you generate a random number between two numbers in your query using the random() function? Thank's. select random() % (:high - :low) + :low; Igor Tandetnik sqlite select random()%(10-5)+5; 2 select abs(random()) % (:high - :low) + :low; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Support EXTRACT SQL standard function
2012/2/10 Willian Gustavo Veiga wilt...@gmail.com:
SQLite is a great database to unit test (TDD) applications. You can run it
in memory with your tests ...
I've found a problem when I was unit testing my application. MySQL
(production database) supports EXTRACT SQL standard function. SQLite don't
support it. It would be great to have support in this standard.
http://www.sqlite.org/lang_datefunc.html
MySQL examples:
Input:
SELECT EXTRACT(DAY FROM CURRENT_DATE)
mysql SELECT DAY(CURRENT_DATE);
sqlite SELECT strftime('%d',CURRENT_DATE);
Input:
SELECT EXTRACT(
MONTH FROM CURRENT_DATE )
mysql SELECT MONTH(CURRENT_DATE);
sqlite SELECT strftime('%m',CURRENT_DATE);
Input:
SELECT EXTRACT( YEAR FROM CURRENT_DATE )
mysql SELECT YEAR(CURRENT_DATE);
sqlite SELECT strftime('%Y',CURRENT_DATE);
Unfortunately, strftime isn't a solution. It's not a standard.
Function strftime is your solution. Write two models. One for MySQL,
one for SQLite. These databases are quite different and require
different SQL queries.
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
2012/2/10 Sreekumar TP sreekumar...@gmail.com: Though the example of $ is very intuitive, I am not suggesting that we drop one of the transaction and block the database forever (as it is happening now). Instead, it could be serialized such that two $100 transactions are committed to the db. A situation in which I read from the database first and then changes the data tells me that they are wrong questions. It is such a problem to insert SELECT into UPDATE or INSERT? -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
2012/2/10 Simon Slavin slav...@bigfraud.org: On 10 Feb 2012, at 5:32pm, Kit wrote: A situation in which I read from the database first and then changes the data tells me that they are wrong questions. It is such a problem to insert SELECT into UPDATE or INSERT? Why do you need to do a SELECT at all ? Can you present us with a simple example of your situation ? Simon. Add value from Alpha to Bravo: Wrong: SELECT val AS val1 FROM t1 WHERE name='Alpha'; UPDATE t1 SET val=val+val1 WHERE name='Bravo'; Good: UPDATE t1 SET val=val+(SELECT val FROM t1 WHERE name='Alpha') WHERE name='Bravo'; No explicit transaction, no problem. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Interpolation
2012/2/8 Steinar Midtskogen stei...@latinitas.org:
1. I'd like to be able to look up any timestamp between the oldest and
the newest in the database, and if there is no value stored for that
timestamp, the value given should be an interpolation of the two
closest. So, if the table has:
1325376000 (Jan 1 2012 00:00:00 UTC) | 5.0
1325376300 (Jan 1 2012 00:05:00 UTC) | 10.0
and I do SELECT temp FROM temperature WHERE unix_time = 1325376120
(00:02:00) I should get 7.0.
create table tp (time default (datetime('%s','now')), temp real);
insert into tp values (1328700574,2.5);
insert into tp values (1328701004,4.8);
insert into tp values (1328701060,5.1);
insert into tp values (1328701093,5.2);
select datetime(time,'unixepoch'),temp from tp;
-- 2012-02-08 11:29:34|2.5
-- 2012-02-08 11:36:44|4.8
-- 2012-02-08 11:37:40|5.1
-- 2012-02-08 11:38:13|5.2
SELECT a.temp+(b.temp-a.temp)/(b.time-a.time)*(strftime('%s','2012-02-08
11:37:00')-a.time) FROM
(select time, temp FROM tp
WHERE strftime('%s','2012-02-08 11:37:00')*1=time
ORDER BY time DESC LIMIT 1) AS a,
(select time, temp FROM tp
WHERE strftime('%s','2012-02-08 11:37:00')*1time
ORDER BY time LIMIT 1) AS b;
-- 4.88571428571429
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Import File with Mixed Delimiters
2012/1/9 rpallotta evilspambuc...@gmail.com: I need to import a file to a table but there are two delimiters used in the file; quotes and spaces. The first 2 columns are for metadata and use quotes, the remaining columns are financial data (a column for each month) separated by spaces. For example: Net Sales New York 1000.00 999.00 1112.00 Expenses New York 555.00 600.00 500.00 ?php $f = fopen(example.csv, r); $data=fgetcsv($f, 1000, ); print_r($data); ? [0] = Net Sales [1] = New York [2] = 1000.00 [3] = 999.00 [4] = 1112.00 -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] load regexp noninteractive
echo select * from a where field1 REGEXP '$foo'; | /usr/bin/sqlite3 mydb.db /usr/bin/sqlite3 mydb.db EOT select * from a where field1 REGEXP '$foo'; EOT 2012/1/5, Hajo Locke hajo.lo...@gmx.de: Hello List, i use sqlite 3.6.22 Ubuntu 10.04 I want to use REGEXP in my queries but dont find a way to load the lib in noninteractive Mode. There is no -load Parameter for sqlite3 Binary. I tried to give 2 commands separated by ; in sql-query but this also dont works. All i want to do is to run a query on console like: /usr/bin/sqlite3 mydb.db select * from a where field1 REGEXP '$foo'; What to do now? Thanks, Hajo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] load regexp noninteractive
2012/1/5, Hajo Locke hajo.lo...@gmx.de: Hello, thanks, 2nd is working. i do in perl now something like: Next time write: I want it for Perl. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to sort within a text field
2012/1/3 guiz guizaw...@gmail.com:
Hi all, if I have a table like
CREATE TABLE [a] (
[ID] INTEGER NOT NULL,
[prdtype] VARCHAR(35) NOT NULL
)
and data already inserted as
insert into a (ID, prdtype) values (1, '15');
insert into a (ID, prdtype) values (2, '15|10|27|3');
insert into a (ID, prdtype) values (3, '8|6|22');
and I'd like to update table 'a' to achieve sorted result in prdtype as ...
Rick
PHP experiment:
?php
function phpsort($str) {
$a=explode('|',$str);
asort($a);
return implode('|',$a);
}
$db=new PDO('sqlite:test.db');
$db-sqliteCreateFunction('usort','phpsort');
$db-query('UPDATE a SET prdtype=usort(prdtype);');
?
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to sort within a text field
2012/1/3 Simon Slavin slav...@bigfraud.org: SELECT id,group_concat(type) FROM (SELECT ID, prdtype FROM prds ORDER BY id, prdtype); Simon. SELECT id,group_concat(prdtype) FROM (SELECT ID, prdtype FROM prds ORDER BY id, prdtype) GROUP BY id; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient usage of sqlite
2012/1/2 Baruch Burstein bmburst...@gmail.com: My resources are a bunch of sound and image files, level data files, script files and other game data stuff. Instead of distributing my game with about 20-30 small (some very small) files, I thought I would roll all the files into some kind of archive. I started to roll my own format, when it occurred to me that sqlite may be well suited for this. Which brought me to wonder if storing 5-10 tables with some of them having 500 bytes of data may be very inefficient. I don't want to substitute a 20K file for my 10K of files. I know this is not a lot, but it still bothers me, like what when I have a game with 500M of files? (you never know, it may happen!). No searching is needed (except once for the key to load a resource) Such data in SQLite take up less space than in the file system. You can save data in one table, see above. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient usage of sqlite
2012/1/1 Baruch Burstein bmburst...@gmail.com: I need a file format to hold a bunch of resources for my program. I thought of using SQLite. However, I am debating two formats. The more convenient one for me would put every few resources in a separate table. However, this would result in small tables. Am I right that this is very inefficient in SQLite? The other option would be to put a bunch of unrelated resources in one table. Is this more efficient? It is preferable to have fewer large tables, mainly due to ease of maintenance, but in the case of independent data that can be otherwise. Best to try both. What count of tables are we talking? Hundreds or thousands are not a problem. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search
2011/12/27 Durga D durga.d...@gmail.com:
select * from virfts4 where residence match '/*'; -- dint work
how to get counties names from this db by using query?
Normalize database to 1NF, e.g.
CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
arrivtime, duration, imagelocation);
INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
0730, 1500,'C');
then use select:
SELECT DISTINCT country FROM virfts4;
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search
2011/12/27 Durga D durga.d...@gmail.com: HI all, I have sqlite database with more than 1000 records. Here, residencearea is the primary key. /country/state/city/village I tried with fts3 and fts4 virtual tables. Not much performance. like query on direct table, dead slow. Send your SQL query and table structure. Maybe you used LIKE instead of MATCH. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] why primary key can changed?Id changed is 2, is bug?(UName is unique)
2011/12/25 get bridges softcrea...@163.com:
create table IF NOT EXISTS Unions
(
Id integer primary key autoincrement,
UName nvarchar(500) unique,
Filter bit not null default 0
);
replace into Unions(UName,Filter) values('a','True')--Source Id is 1
replace into Unions(UName,Filter) values('a','False')//Changed Id is 2
why primary key can changed?Id changed is 2,is bug?
REPLACE is an alias of INSERT OR REPLACE.
http://www.sqlite.org/lang_replace.html
The record is inserted, then duplicity is deleted. If you want to
update record, use the UPDATE.
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] : about add sqlite database cloumn
2011/12/23 YAN HONG YE yanhong...@mpsa.com:
I have a question about C binding for sqlite, I have a table like this:
Name Price1 Price2 Sum
A1 23 231
A2 22 12
A3 21 223
how to use functin
int myfunc() {
int tt=0;
if (price12) tt++;
if (price21) tt++;
if (price212) tt++;
return tt
}
to put function result into my table last added cloumn use sqlite in c code?
SELECT (price12)+(price21)+(price212) AS tt FROM table;
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] datetime
2011/12/20 Stephen Chrzanowski pontia...@gmail.com:
I live in GMT-5 (America/Toronto). Current time is 8:06am, which should be
13:06Z. However, according to this:
select datetime('now','localtime'),datetime('now','utc');
datetime('now','localtime') datetime('now','utc')
2011-12-20 08:05:24 2011-12-20 18:05:24
Try
select datetime('now');
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite ordering data from multiple columns
2011/12/2, colombus gijo...@gmail.com: I want to search this database I will search Emp Name Emp Desc for example If Search EMP I will get results as EMP1 , this is EMP1, EMP2, this is EMP2. I need to order this search in such a way that I get the Emp Name Column first then I will get the Emp Desc Column. So the result should be as follows. EMP1, EMP2, this is EMP1, this is EMP2. Is it possible to implement this in one query in Sqlite ??? SELECT id, name FROM emp WHERE name LIKE '%emp%' UNION ALL SELECT id, descr FROM emp WHERE descr LIKE '%emp%'; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql server management studio like tool for SQLite
2011/11/5 John Horn pagemeis...@sbcglobal.net: Kit, I've tried many of the tools listed @ http://www.sqlite.org/cvstrac/wiki?p=ManagementTools. My hands-down vote is for SQLiteExpert Professional @ http://sqliteexpert.com/http://sqliteexpert.com/. In my opinion spending $59 for the Pro version is a **no-brainer** for many reasons. John John, SQLite Expert look nice, but it's for Windows platform only. Nothing for my OS. BTW: I prefer line SQL instead. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql server management studio like tool for SQLite
2011/11/4 David Hubbard dgxhubb...@gmail.com: Is there any tool for SQLite like sql server management studio? We are looking at using SQLite and I have no expireince with it but would like an easy to use tool to use with SQLite that can perform the same functions as SSMS. http://www.adminer.org/ -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique id
2011/10/27 Tim Streater t...@clothears.org.uk:
Is there a way to get a unique id from a database without actually creating a
new row? If I have:
Cheers -- Tim
sqlite CREATE TABLE test (id integer primary key autoincrement, name text);
sqlite INSERT INTO test (name) VALUES ('xx');
sqlite BEGIN;
sqlite UPDATE sqlite_sequence SET seq=seq+3 WHERE name='test';
sqlite SELECT seq FROM sqlite_sequence WHERE name='test';
4
sqlite END;
sqlite INSERT INTO pokus (name) VALUES ('xxx');
sqlite SELECT * FROM test;
1|xx
5|xxx
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.
2011/10/25 Bo Peng ben@gmail.com: Tables are added in batch and then kept unchanged. I mean, a database might have 1000 new tables one day, and 2000 later. All operations are on single tables. Each table is for one 'sample'. All tables have one column for 'item id', and optional (zero - perhaps 6) columns for item attributes, which can be INT or FLOAT. Is your 'id' type INTEGER PRIMARY KEY? Each table in SQLite have a hidden key 'rowid INTEGER PRIMARY KEY'. You may merge it with 'id'. There is no index and no key because I was concerned about insertion performance and size of database. My understanding is that index or key would not help simple aggregation operations because all records will be iterated sequentially. If you do not need indexes, you can use a simpler solution: CSV. One sample (now a table) - one CSV file. Searching will be faster. I am running SELECT COUNT(*), MAX(QT) FROM TABLE_XX; etc as long as the WHERE clause is the same. You can create additional indexes after inserting data into a table. To search for maximum serve well. If the content of tables also constant, it is possible after a creating to generate the aggregated values? For additional search would only use this summary data. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
2011/10/18 Sune Ahlgren sune_ahlg...@hotmail.com: What can I do to make SQLite run safely on CIFS? /Sune Do not use SQLite on shared device. Use client/server database or client/server front-end of SQLite. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit COUNT
2011/10/16 Fabian fabianpi...@gmail.com: How can you limit a count-query? I tried: SELECT COUNT(*) FROM table LIMIT 5000 SELECT min(COUNT(*),5000) FROM table; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit COUNT
2011/10/16 Petite Abeille petite.abei...@gmail.com: On Oct 16, 2011, at 10:39 PM, Kit wrote: select count(*) from (select 1 from table limit 5000) SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000); you realize that count( * ) has a very specific meaning, right? The count(*) function (with no arguments) returns the total number of rows in the group. http://www.sqlite.org/lang_aggfunc.html If this is what you mean, then stick to it :) I originally thought that the symbol * means all columns. I tried to EXPLAIN, now I see it differently. Thank you. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit COUNT
select count(*) from (select 1 from table limit 5000) SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000); -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
2011/8/22 Black, Michael (IS) michael.bla...@ngc.com: sqlite create table t1(id integer primary key autoincrement,data text); : sqlite select rowid,* from t1; : 5|5|two This should be 4,5 and not 5,5 according to the docs as 3 was the largest in the table prior to insert. Column `id` is an alias `rowid`, 3rd paragraph: http://www.sqlite.org/lang_createtable.html#rowid -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shell doesn't do TABLE/TABLE
2011/8/10 Simon Slavin slav...@bigfraud.org: I've never tried using this before for some reason but in a recent OS X version of the command-line shell I tried using .mode html today. The content is fine, but it doesn't do TABLE or /TABLE. Intentional ? Bug ? Oversight ? Trying hard to believe I'm not the first person who has tried this. If someone feels like fixing this, then it should also include TBODY and /TBODY as well, but most browsers will infer these. If someone claims 'no fix because we have users who rely on this' is there any chance of another mode, perhaps 'htmlfull' which does this ? Simon. Much more I dislike that the tags are uppercase on output. I prefer lowercase, so this functionality can not be used practically. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shell doesn't do TABLE/TABLE
2011/8/10 Brian Curley bpcur...@gmail.com: Depending on your preferred shell...the sqlite CLI is just crazy flexible. Just pipe your output through sed for upper/lower preferences. It is not entirely primitive. It needs only tags in lowercase. cite href=http://www.sqlite.org/sqlite.html; The last output mode is html. In this mode, sqlite3 writes the results of the query as an XHTML table. /cite XHTML tags are in lowercase... -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shell doesn't do TABLE/TABLE
2011/8/10 Simon Slavin slav...@bigfraud.org:
However, the tags the shell tool generates are upper case. There are two
possible fixes:
a) change the tool to generate lower-case tags.
b) change the documentation to say that the tags are HTML, not XHTML.
Simon.
OK, here is the patch.
--
Kit
diff --git a/src/shell.c b/src/shell.c
index a54c922..337f35a 100644
--- a/src/shell.c
+++ b/src/shell.c
@@ -746,22 +746,22 @@ static int shell_callback(void *pArg, int nArg,
char **azArg, char **azCol, int
}
case MODE_Html: {
if( p-cnt++==0 p-showHeader ){
-fprintf(p-out,TR);
+fprintf(p-out,tr);
for(i=0; inArg; i++){
- fprintf(p-out,TH);
+ fprintf(p-out,th);
output_html_string(p-out, azCol[i]);
- fprintf(p-out,/TH\n);
+ fprintf(p-out,/th\n);
}
-fprintf(p-out,/TR\n);
+fprintf(p-out,/tr\n);
}
if( azArg==0 ) break;
- fprintf(p-out,TR);
+ fprintf(p-out,tr);
for(i=0; inArg; i++){
-fprintf(p-out,TD);
+fprintf(p-out,td);
output_html_string(p-out, azArg[i] ? azArg[i] : p-nullvalue);
-fprintf(p-out,/TD\n);
+fprintf(p-out,/td\n);
}
- fprintf(p-out,/TR\n);
+ fprintf(p-out,/tr\n);
break;
}
case MODE_Tcl: {
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Maximum number of tables in a data file
2011/8/9 Jaco Breitenbach jjbreitenb...@gmail.com: I am building an application that filters out duplicate input data by generating an MD5 hash of each input, and implicitly comparing that against a set of keys already stored in the SQLite database by doing an insert into a unique-indexed table. If the insert fails, a duplicate is assumed, otherwise the new unique key is stored, and the input processed. Jaco Try Kyoto Cabinet or LevelDB. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Maximum number of tables in a data file
2011/8/9 David Garfield garfi...@irving.iisd.sra.com: Having said that, let me present a database for consideration: Any filesystem. Split the hex of the MD5 into directory levels and make what you need. Might be slower, particularly with some OSes, but the tools are easy. --David Garfield This is not a good idea. It will be very slow. Databases are much more suitable for small blocks of data. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Text searching
2011/8/5 Mohit Sindhwani m...@onghu.com: We have a list of words that we want to quickly search. We used FTS3 for it and it is fast and works very well. The only problem is that it breaks the original entry at word boundaries (spaces, punctuation, etc.). We would like to allow a person to search starting within a word. So, we want to do something that gives FTS like speed for queries like LIKE '%s%' What would you recommend? We've tried a few things, none of them elegant.. yet! The original table has around 650,000 names (2 - 5 words per name). Best Regards, Mohit. I tried to use the Tokyo Cabinet (btree) with very good results. I created a custom index of substrings substring(string, 1)||' '||id .. substring(string, length)||' '||id Time to search in 1M records (16M records in the index) is in ms. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange result using JOIN
2011/7/22 Sintoni Stefano (GMAIL) stefano.sintoni.cnigr...@gmail.com: More thanks, I understand that but why the same query, with the same data, return two different result using different SQLite-based Win32 applications ? In any case how I can take the result I need ? I not have other column right for made one ORDER BY and I need to respect the sequence or CODE table like Any idea ? Stefano INSERT INTO ELAB SELECT CODE.* FROM JOB JOIN CODE ON CODE.CODE=JOB.CODE ORDER BY JOB.rowid,CODE.rowid; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multidimensional representation
2011/7/6 e-mail mgbg25171 mgbg25...@blueyonder.co.uk: 1990 1991 year = dimension north sales 10 8 cogs (5) (4) south sales 6 5 cogs (2) (1) region line item == dimensions create table stat(region, item, year, value); insert into stat values(north,sales,1990,10); insert into stat values(north,sales,1991,8); : insert into stat values(south,cogs,1991,-1); I just want to be able to specify different dimensional values and get back rectangles of data that I can manipulate. I've already done the manipulation stuff so it's just how to layout the data. Query examples would be... total = north + south returning 9, 8 select region,sum(value) as total from stat group by region; total cogs = north.cogs + south.cogs returning -7, -5 select year,sum(value) as total from stat where item=cogs group by year; 1990 returning 10,-5,6,-2 select region,item,value as total from stat where year=1990; north:1991 returning 8,-4 select item,value as total from stat where region=north and year=1991; north:1990..1991 returning 10,8 select year,sum(value) as total from stat where region=north and item=sales group by year; Once you've created region I don't think you can delete it and all it's entries cos that would delete everything i.e. I believe you have to leave at least one value in the column whether it be null or north. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VS2010 Issue
Any help or direction is appreciated. I have a VS 2005 application using SQLite 1.0.66.0 and Net Framework 2.0. I'm trying to take the applicaiton to a Windows 7 64 bit machine but not sure what I need to do to convert SQLite to use Net Framework 4. Is this even possible and how? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VS2010 Issue
Sorry I should have added that I converted the project to VS 2010. --- On Wed, 5/11/11, Kit Pat kitpat1...@yahoo.com wrote: From: Kit Pat kitpat1...@yahoo.com Subject: [sqlite] VS2010 Issue To: sqlite-users@sqlite.org Date: Wednesday, May 11, 2011, 12:56 PM Any help or direction is appreciated. I have a VS 2005 application using SQLite 1.0.66.0 and Net Framework 2.0. I'm trying to take the applicaiton to a Windows 7 64 bit machine but not sure what I need to do to convert SQLite to use Net Framework 4. Is this even possible and how? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create one index on multiple columns or create multiple indexes, each of which is on one column?
2010/7/18 Peng Yu pengyu...@gmail.com: This question may be trivial. But I'm wondering, whether I should create one index on multiple columns or create multiple indexes, each of which is on a single column. select * from test group by value1,value2; select * from test group by value1||value2; For this case, what is the best way to create the index? -- Regards, Peng 1. create index test1 on (value1,value2); create index test2 on (value2); 2. Try EXPLAIN. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a rowid from a view
2010/5/29 Jim Terman jter...@tivo.com: Say I have a table of phone numbers CREATE TABLE phonebook (first_name TEXT, last_name TEXT, phone_number TEXT); I want to sort this by name, so I create a view CREATE VIEW phonebook_order AS SELECT first_name, last_name, phone_number FROM phonebook ORDER BY last_name, first_name; CREATE VIEW phonebook_order AS SELECT rowid, first_name, last_name, phone_number FROM phonebook ORDER BY last_name, first_name; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG in SQLite? Still the rowid question
2009/8/29 Wanadoo Hartwig hartwig.wiesm...@wanadoo.nl:
The largest and last row id is 4. Why is SQLite returning 5? I think
it has to do with the FTS3 module but still the trigger statement
should shield the row ids from the trigger statement, or?
Hartwig
CREATE TABLE Simple (ID integer primary key, Name text);
CREATE TABLE SimpleFTS (Name);
CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW
BEGIN
DELETE FROM SimpleFTS WHERE (rowid=OLD.ID);
END;
CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW
BEGIN
INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name);
END;
INSERT INTO Simple (Name) VALUES('one');
INSERT INTO Simple (Name) VALUES('two');
DELETE FROM Simple WHERE (ID = 1);
INSERT INTO Simple (Name) VALUES('three');
SELECT * FROM Simple;
2|two
3|three
SELECT last_insert_rowid() FROM Simple;
3
3
Perfect.
sqlite CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);
SQL error: no such module: FTS3
What's FTS3? http://dotnetperls.com/sqlite-fts3 ?
citeVirtual tables are a new feature in SQLite (currently still only
available from the development version on CVS)/cite
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Insert into 2 tables
Hi there, I have single table: CREATE TABLE booklist(author TEXT, book TEXT); --- Tolkien The Hobbit Hemingway The Old Man and the Sea VerneTwenty Thousand Leagues under Hemingway A Farewell to Arms Tolkien The Lord of the Rings I want split it into 2 tables: CREATE TABLE authors(author TEXT); CREATE TABLE books(authorID INT, book TEXT); CREATE INDEX i_authors ON authors (author); INSERT INTO authors SELECT DISTINCT author FROM booklist; INSERT INTO books (authorID, book) SELECT authors.rowid, booklist.book FROM authors,booklist WHERE authors.author=booklist.author; - The algorithm run fine (maybe you know better), but when I want insert next booklist with one the same author, author will be added twice. Create unique index on authors block all adding rows. Have you a better solution? -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert into 2 tables
2009/8/27 Igor Tandetnik itandet...@mvps.org: Or else, create the unique index on authors.author as originally planned, then use INSERT OR IGNORE to skip over errors. Igor Tandetnik Super! I lost some hours for finding a solution today. -- Kit CREATE TABLE booklist(author TEXT, book TEXT); CREATE TABLE authors(author TEXT); CREATE TABLE books(authorID INT, book TEXT); CREATE UNIQUE INDEX i_authors ON authors (author); INSERT OR IGNORE INTO authors SELECT author FROM booklist; INSERT INTO books (authorID, book) SELECT authors.rowid, booklist.book FROM authors,booklist WHERE authors.author=booklist.author; meta keywords: insert into multiple tables ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Delphi] Reading list of tables?
2009/8/27 Gilles Ganault gilles.gana...@free.fr: BTW, I noticed that sqlite_master has two columns that return the same thing: What is the difference between name and tbl_name? It's different for indexes, wiews and triggers. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Tools
2009/8/25 Barton Torbert btorb...@fbdover.com: I am looking for one of two things. The first thing would be a tool to easily synchronize data and structure between SQLite and Access. I want to be able to do something in one system and have it exported to the other. Bart http://www.sqlite.org/cvstrac/wiki?p=ConverterTools -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite for GIS Application
2009/8/23 Itzchak Raiskin itzchak.rais...@gmail.com: Hi I want to use SQLite in a GIS application where I create a database containing terrain data (coordinates, height). I would like to query this database with start and end points of a line and get a vector with all heights point along this line. I can, of course create a query for each point along the line, but this will be very time consuming as I have hundreds of lines with hundreds of points. Any suggestions? Thanks, Itzik SELECT x,y,height FROM terrain WHERE round(a*x+b*y+c)=0 AND x BETWEEN xmin AND xmax AND y BETWEEN ymin AND ymax; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite for GIS Application
2009/8/23 P Kishor punk.k...@gmail.com: If a line is expressed by (y = ax + c), you need to find all x,y that will satisfy that equation. So, SELECT x, y, z FROM terrain WHERE a*x - y + c = 0 General equation of line is a*x + b*y + c = 0. If you delete parameter b, you will have a problem with some pair of points - e.g. [0,0],[0,1]. Better way to save unit squares to file is a simple 2D matrix. No database. Fast and easy for samples with fixed steps of coordinates. You can load entire matrix into memory. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is rowid sequential?
2009/8/21 John Loveys john.lov...@nautel.com: Hi All I am hoping the rowid is sequential. Then I can simply get the biggest rowid (using sqlite3_last_insert_rowid), subtract N from it and delete rows where rowid last_rowid - N. Can anyone confirm the rowid is guaranteed sequential in my scenario? Are the any gotchas I should know about? Thanks, John http://www.sqlite.org/autoinc.html cite/ If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. /cite Yes -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTime comparison with custom format
2009/8/21 t-master t-zimmerm...@onlinehome.de: Hi I have string in a table representing a DateTime. The format is 21.08.2009 00:25:00 And I would like to compare it to now How can I do this? -- View this message in context: http://www.nabble.com/DateTime-comparison-with-custom-format-tp25085040p25085040.html Sent from the SQLite mailing list archive at Nabble.com. I recomend to convert dateformat to julianday() http://www.sqlite.org/lang_datefunc.html -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Linking all the column fields together (or something like this)
2009/8/20 ZB z...@ispid.com.pl: I need to search a table, which has - say - about 20 columns. Each column should be checked against given string (phrase), in usual manner: SELECT something FROM table WHERE col1 LIKE '%phrase%' OR col2 LIKE '%phrase%' OR col3 LIKE '%phrase%' ... OR col20 LIKE '%phrase%' I'm afraid, it will be inefficient. Perhaps better would be to concat all the fields together, and search for first occurence of phrase only that big string? Does there exist any possibility to make it such way? Or perhaps there's other, better solution? pozdrawiam / regards Zbigniew SELECT something FROM table WHERE (col1||';'||col2||...||col20) LIKE '%phrase%' -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Copying an open db file
2009/8/20 Angus March an...@uducat.com: I want to copy a db file while it is still open, and I'm wondering how safe that is. .dump -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select data from 2 lines in one line?
2009/8/19 Mário Anselmo Scandelari Bussmann mario.bussm...@gmail.com: I have a table like this: petr4 --- rowid|data|preabe|premax|premin|preult|voltot 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0 3|2007-01-04|48.5|48.75|47.4|47.65|505916186.0 4|2007-01-05|47.2|47.99|45.1|46.19|581485748.0 5|2007-01-08|46.5|47.14|45.8|46.59|452501627.0 6|2007-01-09|45.97|46.39|44.61|45.52|587958198.0 7|2007-01-10|44.51|45.26|44.3|45.25|470899428.0 8|2007-01-11|44.9|46.43|44.76|45.21|542684895.0 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0 I need a select that returns data,preult,previous data and previous preult: 2007-01-03|48.7|2007-01-02|50.45 2007-01-04|47.65|2007-01-03|48.7 2007-01-05|46.19|2007-01-04|47.65 2007-01-08|46.59|2007-01-05|46.19 2007-01-09|45.52|2007-01-08|46.59 2007-01-10|45.25|2007-01-09|45.52 2007-01-11|45.21|2007-01-10|45.25 2007-01-12|45.15|2007-01-11|45.21 2007-01-15|44.89|2007-01-12|45.15 SELECT data,preult,temp.data,temp.preult FROM petr4,petr4 AS temp WHERE petr4.rowid=temp.rowid-1; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select data from 2 lines in one line?
Right form (tested): SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS previous_data,temp.preult AS previous_preult FROM petr4,petr4 AS temp WHERE petr4.rowid=temp.rowid+1; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select data from 2 lines in one line?
2009/8/19 John Machin sjmac...@lexicon.net: On 20/08/2009 12:57 AM, Kit wrote: Right form (tested): SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS previous_data,temp.preult AS previous_preult FROM petr4,petr4 AS temp WHERE petr4.rowid=temp.rowid+1; Don't you think that relying on (a) rowid being consecutive (b) rowid order being identical to date order is just a little bit dodgy? What if the table has been created by a bulk load, not necessarily in ascending date order? What if some rows have been deleted? OK, you found more better solution. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select data from 2 lines in one line?
2009/8/19 Mário Anselmo Scandelari Bussmann mario.bussm...@gmail.com: Both work for me, Kit solution is very fast, but I think John is right. In my case, the tables have sequencial rowid. If I delete some row, then will not work anymore. When you create an index of column data, John's solution will be fast too. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT with multiple VALUES clause?
2009/8/18 Terrence Brannon scheme...@gmail.com: Hello, when converting a .sql file containing MySQL INSERT clauses, one often runs into the problem that the MySQL INSERT can accept multiple VALUES arguments: INSERT INTO actor VALUES (1,'PENELOPE','GUINESS','2006-02-15 04:34:33'),(2,'NICK','WAHLBERG','2006-02-15 04:34:33'),(3,'ED','CHASE','2006-02-15 04:34:33') whereas the SQLite INSERT can only take one... Is there any possibility of extending the syntax of the SQLite insert to accept multiple VALUES arguments? INSERT INTO actor SELECT 1,'PENELOPE','GUINESS','2006-02-15 04:34:33' UNION ALL SELECT 2,'NICK','WAHLBERG','2006-02-15 04:34:33' UNION ALL SELECT 3,'ED','CHASE','2006-02-15 04:34:33'; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
