Re: [sqlite] Logging only SQL commands which make changes, parsing PRAGMAs
2012/8/23 Simon Slavin : > 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 : > The sqlcmd is: > insert into mydb (co1,co2) select count(*) from db1 where db1.sco1>80 ,select > count(*) from db2 where db2.sco2>90; > 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 sco1>80), (SELECT count(*) FROM db2 WHERE sco2>90); -- 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 : > I wanna insert into a value to my sqlte db: > > insert into mydb (number1) select count(number1) from db2 where number1>80; > > 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 number1>80; 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 : > Here is another way I found out how insert UTF-8 strings in SQLITE3.EXE. > > F:\sqlite3_6_16>sqlite3.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 : > 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] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value
2012/5/9 Petite Abeille : > 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] Error: database is locked on redhat6
2012/4/21 田晶 : > 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] BUG: No reliable way to import strings from a file
2012/4/21 Braddock Gaskill :
>
> 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 <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] how much "case...when..." command?
2012/4/17 YAN HONG YE : > UPDATE bb SET Slevel = > CASE price1>12 WHEN 1 THEN 1 ELSE 0 END + > CASE price1>30 WHEN 1 THEN 1 ELSE 0 END + > CASE price2>20 WHEN 1 THEN 1 ELSE 0 END + > CASE price2>30 WHEN 1 THEN 1 ELSE 0 END + > case... > csse... > ... > CASE price2>80 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 Petite Abeille : > 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_onhttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] auto-incrementing integer in composite primary key
2012/4/16 Puneet Kishor : > 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 Puneet Kishor : > 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] Why are two select statements 2000 times faster than one?
2012/4/15 Steinar Midtskogen : > 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 : > 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 : > 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 : > 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/12, Christoph P.U. Kukulies : >> 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/9 Christoph P.U. Kukulies : 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] UPDATE failure
2012/3/6 : > 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] 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] TEXT PRIMARY KEY
2012/3/4 Christoph P.U. Kukulies : >> 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/4 Christoph P.U. Kukulies : > 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 : > 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/3 Christoph P.U. Kukulies : > 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 : > 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 黃楨民 : > 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 : > 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 : > bhaskarReddy 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] Support EXTRACT SQL standard function
2012/2/10 Willian Gustavo Veiga :
> 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] SQLite Random number generator
2012/2/11 Igor Tandetnik : > 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] Database locked in multi process scenario
2012/2/10 Simon Slavin : > 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] Database locked in multi process scenario
2012/2/10 Sreekumar TP : > 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] Interpolation
2012/2/8 Steinar Midtskogen :
> 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')*1http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Import File with Mixed Delimiters
2012/1/10 rpallotta : > Thanks for the reply, but do I necessarily need to use php? I'd like the > ability to do it from the SQL command window using a variation of the > .import command, if possible. That was my first idea, because I work with SQLite from PHP mainly. I can not think of anything else, perhaps modify SQLite shell or modify input data by some filter. -- 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 : > > 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 [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
2012/1/5 Hajo Locke : > >> Next time write: "I want it for Perl". > > why? your answer would be the same if we talk about running a shellcommand? > the scripting language which runs the call should be irrelevant... > not? http://mailliststock.wordpress.com/2007/03/01/sqlite-examples-with-bash-perl-and-python/ -- Kit ___ 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 : > 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] load regexp noninteractive
echo "select * from a where field1 REGEXP '$foo';" | /usr/bin/sqlite3 mydb.db /usr/bin/sqlite3 mydb.db <: > 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] How to sort within a text field
2012/1/3 guiz :
> 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:
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 : > 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 : > 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 : > 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 :
> 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 : > 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 :
> 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 :
> 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 (price1>2) tt++;
> if (price2>1) tt++;
> if (price2>12) tt++;
> return tt
> }
>
> to put function result into my table last added cloumn use sqlite in c code?
SELECT (price1>2)+(price2>1)+(price2>12) 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 :
> 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] union-having bug
2011/12/3 Gillman, David : > Is this behavior known? The third query returns no rows even though bar = 1. > sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 > bar union select 1 ind, 1 foo, 0 bar) group by ind having bar > 0; select ind, sum(foo) fooo, sum(bar) barr from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind having barr >0; 1|1|1 -- 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 : > 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 : > 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 : > 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 :
> 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 : > 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 : > 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 : > 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 : > 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) : > 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
2011/8/10 Simon Slavin :
> 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,"");
+fprintf(p->out,"");
for(i=0; iout,"");
+ fprintf(p->out,"");
output_html_string(p->out, azCol[i]);
- fprintf(p->out,"\n");
+ fprintf(p->out,"\n");
}
-fprintf(p->out,"\n");
+fprintf(p->out,"\n");
}
if( azArg==0 ) break;
- fprintf(p->out,"");
+ fprintf(p->out,"");
for(i=0; iout,"");
+fprintf(p->out,"");
output_html_string(p->out, azArg[i] ? azArg[i] : p->nullvalue);
-fprintf(p->out,"\n");
+fprintf(p->out,"\n");
}
- fprintf(p->out,"\n");
+ fprintf(p->out,"\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] Shell doesn't do
2011/8/10 Brian Curley : > 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. 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. 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
2011/8/10 Simon Slavin : > 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 or . > 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 and > 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] Maximum number of tables in a data file
2011/8/9 David Garfield : > 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] Maximum number of tables in a data file
2011/8/9 Jaco Breitenbach : > 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] Text searching
2011/8/5 Mohit Sindhwani : > 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) : > 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 :
> 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
Re: [sqlite] VS2010 Issue
Sorry I should have added that I converted the project to VS 2010. --- On Wed, 5/11/11, Kit Pat wrote: From: Kit Pat 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
[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] create one index on multiple columns or create multiple indexes, each of which is on one column?
2010/7/18 Peng Yu : >>> 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 : > 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 :
> 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 ?
Virtual tables are a new feature in SQLite (currently still only
available from the development version on CVS)
--
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 : > 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
[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] [Delphi] Reading list of tables?
2009/8/27 Gilles Ganault : > 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 : > 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 P Kishor : > 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] Using SQLite for GIS Application
2009/8/23 Itzchak Raiskin : > 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] DateTime comparison with custom format
2009/8/21 t-master : > 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] Is rowid sequential?
2009/8/21 John Loveys : > 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] Copying an open db file
2009/8/20 Angus March : > 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] Linking all the column fields together (or something like this)
2009/8/20 ZB :
> 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] How to select data from 2 lines in one line?
2009/8/19 Mário Anselmo Scandelari Bussmann : > 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] How to select data from 2 lines in one line?
2009/8/19 John Machin : > 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?
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 Mário Anselmo Scandelari Bussmann : > 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] INSERT with multiple VALUES clause?
2009/8/18 Terrence Brannon : > 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
