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
.
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
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
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
-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
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
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
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
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
) 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
=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
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
) 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
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
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
...)
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
. 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
,
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
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
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
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
. Subversion, Git, Mercurial and Bazaar.
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
. 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
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
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
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
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
. 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
);
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
)+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
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
.
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
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
) AS b;
-- 4.88571428571429
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
. 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
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
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
','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
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
.
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
, 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
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
) 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
++;
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
: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
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
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
://www.adminer.org/
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
? 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
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
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
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
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
` 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
'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
;
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
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
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
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
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
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
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
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?
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
;
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
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
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
solution?
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
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
://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
AND ymax;
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
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
-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
?
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
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
|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
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
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
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
: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
84 matches
Mail list logo
