[sqlite] quoted .import
Hi Is there a way to get .import filename table to process quoted columns? aka if .separator "," then any quoted column with "hello, world" will break it. I do not control the exports which are daily derived dumps from mysql and db2 databases. I did a test with unquoted export with separator '_#_' which worked excellent, but this will mess up other programs working on the export files. Thank You all -- Morten Bjoernsvik, Oslo, Norway _ Alt i ett. Få Yahoo! Mail med adressekartotek, kalender og notisblokk. http://no.mail.yahoo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Autocommit .read inserts are so slow it must be a bug
Hi I've been puzzled for some days. I believed I had a hang in my system, but it turns out using the .read file.sql is horribly slow. I believed it to be an improper trigger, but removing it doesn't help a bit. 1min36sec to read 1000 lines with 6 simple columns into one table. I know insert with autocommit was slow, but not that slow. Is this filesystem-io based? it works very hard on the filesystem. I also tried simplifying the primary key, but timing is the same. Memory caching, etc? I can emulate file-inserts faster in a palmpilot simulator. Nice if someone could share some light on this. I'm easily going around this bug with commit, so it is not much of a problem once you know. SQLite 3.3.8 on linux 32bit. % wc -l test_autocommit.sql test_commit.sql 1024 test_autocommit.sql 1026 test_commit.sql 2050 total % time echo ".read test_autocommit.sql" | sqlite3 test.db real 1m36.116s user 0m0.408s sys 0m0.988s % rm -rf test.db If I add Begin transaction and commit it is blasting fast: % time echo ".read test_commit.sql" | sqlite3 test.db real 0m0.309s user 0m0.168s sys 0m0.040s sqlite .schema CREATE TABLE secanaconfig ( name TEXT not null, value TEXT not null, type TEXT not null, grp TEXT, desc TEXT, updated DATE, PRIMARY KEY (name, type) ); CREATE TABLE subtypes ( type TEXT PRIMARY KEY, desc TEXT ); CREATE TRIGGER fki_secanaconfig before insert on secanaconfig FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert into secanaconfig foreign-key type does not exists in subtypes table') WHERE (SELECT type FROM subtypes WHERE type = NEW.type) IS NULL; END; Thanks -- Morten Bjoernsvik, Oslo, Norway _ Alt i ett. Få Yahoo! Mail med adressekartotek, kalender og notisblokk. http://no.mail.yahoo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] thoughts on a web-based front end to sqlite3 db?
> Ease of learning is a plus as I need to get > something basic up and > running fairly fast. I've heard good things about > Python in that respect. > Does anyone have alternative suggestions, or if you > agree that Python Is > Good, would you suggest using APSW, pysqlite, or > something else? Do not underestimate the power of the SQLite console (aka the sqlite3 binary). I've been using php with sqlite module compiled in and perl with DBD::SQLite for long time. but after I wrote a simple 55lines SQL_direct parser for the console, I prefer using it. it gives me full access to all the .commands and input/output blobs via tempfiles. I just build up a file of SQLite commands and SQL queries to be executed and just parse it through like in: $textoutput= `$sqlite $database < $sqlcommandfile`; Blobs are handled via files. -- MortenB
Re: [sqlite] what is "in-memory SQLite database"?
Hi If you have available memory you can create a ramdisk and mount it as a standard filesystem, then put your database there. This is quite trivial, if you have /dev/shm you can use tempfs. aka: #mkdir /mnt/ramdisk #mount -t tempfs -o size=8G /dev/shm /mnt/ramdisk Creates a 8Gig ramdisk. The kernel usually gives you 50% of your memory available to /dev/shm, you can make your ramdisk as large as you want, but avail swapping at all costs. -- MortenB --- jack wu <[EMAIL PROTECTED]> wrote: > > in the documentation: "Appropriate Uses For SQLite" > it > says: > > "it is often easier and quicker to load the data > into > an in-memory SQLite database and use queries with > joins and ORDER BY clauses to extract the data in > the > form and order needed rather than to try to code the > same operations manually" > > i am wondering if there are more > information/documentations on how to do that? > > i am writing an application where i need to define > couple of huge hash tables. i load the data from > disk, > populate the hash tables and use the tables during > execution. can i replace the hash tables using > SQLite? > i am new to SQLite, so any information is helpful. > > jack. > > > > >
Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 (Is it possible?)
Hi John You are completely correct, it was just a hasty example to show that triggers may solve teh problem. If you want a unique variable you probably have to use a 1 row table as an index counter. % cat test.sql -- example showing how to use an increment unique value with sqlite .header on create table test ( num integer, num2integer, infotext, primary key (num) ); -- one value index database for unique indexnumber create table myindex ( id integer, num integer, primary key (id) ); -- trigger which increases num2 with a unique index create trigger increment_sum_in_test after insert on test begin update myindex set num = num+1 where id=0; update test set num2 = ( select num from myindex where id=0 ) where num=new.num; end; -- first set up myindex so the trigger wont complain insert into myindex (id,num) values (0,0); insert into test (num,info) values (null,'number one'); insert into test (num,info) values (null,'number two'); insert into test (num,info) values (null,'number three'); delete from test where info='number three'; delete from test where info='number one'; insert into test (num,info) values (null,'number four'); insert into test (num,info) values (null,'number five'); select num,info,num2 from test; $ sqlite3 text.db < test.sql num|info|num2 2|number two|2 3|number four|4 4|number five|5 -- MortenB --- John LeSueur <[EMAIL PROTECTED]> skrev: > what happens If you do: > > delete from test where num = 2; > insert into test (num, info) values(null, 'number > four'); > > Wouldn't you have: > > num|info|num2 > 1|number one|1 > 3|number three|3 > 3|number four|3 > > John LeSueur > >
Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 (Is it possible?)
Hi You can easily fix this trick with the "insert null to and integer primary key" trick or by using a trigger. Use a reference value you either increase or decrease, or count to set for new values. % cat /data/temp/test.sql .header on create table test ( num integer, num2integer, infotext, primary key (num) ); create trigger total_num_for_test after insert on test begin update test set num2 = ( select count(*) from test ) where num=new.num; end; insert into test (num,info) values (null,'number one'); insert into test (num,info) values (null,'number two'); insert into test (num,info) values (null,'number three'); select num,info,num2 from test; % sqlite3 test.db < /data/temp/test.sql num|info|num2 1|number one|1 2|number two|2 3|number three|3 % -- MortenB --- [EMAIL PROTECTED] skrev: > Hi, > > A soft reminder. Please inform if the below > mentioned request is a > possibility? > > Regards, > Sankara Narayanan. >
[sqlite] Re: calling external functions in triggers
Hi Again
Sorry, the mouse moved and I pressed enter outside the
text-entryfield so it got sent in an unfinished state:
Here is the finished message:
Hi all
I have several tempfiles I like to delete
with a simple systemcall like
system("$rm -rf $tempfile")
is this possible in a simple trigger:
Can we call syscalls from inside sqllite schemas?
CREATE TABLE extracted (
instancefile text,
appendfiletext,
idinteger,
extracted integer,
pmval_sample text,
pmval_align text,
pmval_starttime text,
pmval_endtime text,
nsamples text,
datetime date,
PRIMARY KEY (instancefile),
UNIQUE (id));
CREATE TRIGGER delete_files_extracted
DELETE ON extracted
BEGIN
--How does this work?
--system("rm -f instancefile");
--system("rm -f appendfile");
END
instancefile holds a path like
/usr/tmp/mortenb/network.interface.in.bytes/eth1
I want that file removed anlong with the tableentry
Thanks
--
Morten Bjørnsvik
