Re: [sqlite] UNIQUE constraint on column
Hi Shane, On Wed, 31 Jan 2007 09:29:24 -0500, you wrote: >On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> "Shane Harrelson" <[EMAIL PROTECTED]> wrote: >> > when i try to insert a row into a table that has a UNIQUE constraint >> > on a column, and I get the SQLITE_CONSTRAINT result code because i'm >> > inserting a duplicate value, is there anyway to determine the rowid of >> > the conflict? >> > >> > looking at the internals of the VDBE, i found that the rowid of the >> > conflicting row is pushed on top of the VDBE stack. >> > >> > if i'm willing to violate the interface, i can dereference the rowid >> > from the internals of the VDBE struct. i'd rather not do this... is >> > there a more formal mechanism for getting this value without having do >> > to do a separate "select" query? something like >> > sqlite3_last_insert_rowid() -- ie. sqlite3_last_conflict_rowid()? >> > >> >> The official way to find the conflicting entry is to do a query. >> >> SELECT rowid FROM table WHERE uniquecolumn=?; >> >> -- >> D. Richard Hipp <[EMAIL PROTECTED]> >> > >Thank you for the reply. I assumed this was most likely the case, and >as I said in my original email, I was hoping to avoid having to do a >seperate select query for the sake of speed... especially since the >value I needed was so tantalizing close in the VDBE struct. The SELECT will be quite fast, chances are the required pages of the unique index will still be in the cache. In my opinion it is always better to write portable code, so I would prefer to rely on generic SQL than on yet another implementation specific API. Just my 2 cents. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
On Wed, 31 Jan 2007 18:31:20 -0500, you wrote: >Is cast documented on the sqlite website? I couldn't find it. http://www.sqlite.org/lang_expr.html -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
On Wed, 31 Jan 2007 17:30:29 -0500, you wrote: >BTW, what is the concatenation operator? Standard SQL: string || string -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Function question
Hi Jim, On Thu, 1 Feb 2007 09:08:44 -0500, you wrote: >From the CLP (i.e., w/o user-defined functions) is there any way to get >the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim >spaces from the right/left end of a column value.) As far as I can tell load_extension(X,Y) can be used in the command line program. I usually postprocess output by piping it through awk to solve these kinds of problems. sqlite3 databasefile outfile >jim HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?
On Tue, 6 Feb 2007 15:13:55 -0500, Donald Griggs wrote: >Regarding: "If I want to change data ." > >Sqlite3Explorer is free software, and works as you describe. >I imagine there are several others. I second this wholeheartedly. >http://www.singular.gr/sqlite/ (Not sure if there >will be future releases of this or not) -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?
On Wed, 7 Feb 2007 13:03:09 -0500, you wrote: >I can't get SQLiteExplorer to work with my databases. It always gives >me an "unknown file format" error. I believe it's been quite a while >since it was updated. > >RobR Did you download sqlite3explorer or sqliteexplorer? The latter is meant for sqlite v2.x.x databases, and requires v2.x.x of sqlite.dll and has not been maintained for a long time. Make sure you use sqlite3explorer.exe v2.0 (which is built for sqlite 3.x.x) and put your sqlite3.dll in the same directory. If it fails to work on an existing database, try to create a new one from sqlite3explorer. I use it in combination with sqlite3.dll, version 3.3.12, without any problem. I downloaded it 2006-03-13 and it still works well. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How do I get MATCH and REGEXP
On Fri, 9 Feb 2007 21:11:31 +0100, Rick wrote: >The manual mentions the support for the MATCH and the REGEXP operators. But >if I use it, it doesn't work. Do I have to do something special to install >those operators? http://www.sqlite.org/lang_expr.html tells: "The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a user-defined function named "regexp" is added at run-time, that function will be called in order to implement the REGEXP operator. The MATCH operator is a special syntax for the match() user function. The default match() function implementation raises and exception and is not really useful for anything. But extensions can override the match() function with more helpful logic." >Regards, >Rick van der Lans HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How do I get MATCH and REGEXP
On Sat, 10 Feb 2007 03:43:47 +0100, Rick wrote: >So, if I understand correctly, SQLite does not support >these two operators "out of the box". Right. >Is there an easy way to get user-defined functions for regexp and match? Or >has no one developed those yet? Five days ago Wayne Bloss mentioned the Arke Systems sqlite3 dll MSVC project/source as a starting point to make a compatible extensions dll. Message-ID: <[EMAIL PROTECTED]> Four days ago Mikey C published an extension library in this list. Message-ID: <[EMAIL PROTECTED]> or http://www.nabble.com/Extension-functions-for-SQLite-in-C-for-free-tf3182921.html#a8833684 >Regards, >Rick van der Lans > >-----Oorspronkelijk bericht- >Van: Kees Nuyt [mailto:[EMAIL PROTECTED] >Verzonden: Friday, February 09, 2007 10:55 PM >Aan: sqlite-users@sqlite.org >Onderwerp: Re: [sqlite] How do I get MATCH and REGEXP > > >On Fri, 9 Feb 2007 21:11:31 +0100, Rick wrote: > >>The manual mentions the support for the MATCH and the REGEXP operators. But >>if I use it, it doesn't work. Do I have to do something special to install >>those operators? > >http://www.sqlite.org/lang_expr.html tells: > >"The REGEXP operator is a special syntax for the regexp() user >function. No regexp() user function is defined by default and so >use of the REGEXP operator will normally result in an error >message. If a user-defined function named "regexp" is added at >run-time, that function will be called in order to implement the >REGEXP operator. > >The MATCH operator is a special syntax for the match() user >function. The default match() function implementation raises and >exception and is not really useful for anything. But extensions >can override the match() function with more helpful logic." > >>Regards, >>Rick van der Lans > >HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem with .import
On Sat, 10 Feb 2007 12:09:12 -0800 (PST), you wrote:
>On Thu, 28 Sep 2006, Dennis Cote wrote:
>
>> I suspect you may have trailing spaces at the ends of your lines. The
>> .import command isn't very smart about things like that. Your separator is
>> set to one space, not arbitrary whitespace. It there is another separator
>> after the last field it assumes there is another field there (which might
>> be an empty string) as well.
>
> Well, I'm back with the same problem, but a trailing space at the end of a
>record is not the problem.
>
> I wrote data from a table using 'insert' mode so I could drop and recreate
>the table without having to manually re-enter the data. That part worked
>just fine: the new table schema matches the number and type of fields in the
>data file.
>
> However, when I try to import the data into the table I see this error
>message:
>
>sqlite> .import variable.sql variable
>variable.sql line 1: expected 14 columns of data but found 16
>
> The editor is configured like this:
>
>sqlite> .show
> echo: off
> explain: off
> headers: off
> mode: insert
>nullvalue: ""
>output: stdout
>separator: ","
> width:
>
>and the first line of data is:
>
>INSERT INTO variable VALUES('Vegetation','Amounts, types, and uses of plant
>cover.','Habitats','','External','x
>100','Centroid',0,100,0.2,'Strong','Fuzzy Space','Minimum','Min-max');
>
> There are 14 fields and no extra space trailing the final ';'. The schema
>is attached for reference.
>
> What have I gotten wrong this time, please?
This is not a comma delimited values file like .import could
process, but an SQL script. You can execute it like:
sqlite3 databasefilename .read variable.sql
>Rich
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How do you combine two SQLite databases?
On Sat, 17 Feb 2007 15:27:25 -0500, you wrote: >Hello, > >I have two SQLite database files, stuff1.db and stuff2.db. Each has three >tables within. I want to combine the two so I have one database file, >stuff.db, with 6 tables. How could I combine the databases? I am aware of >the "attach" command, but this just seemed to create a file with two >sub-databases, each of which has its own tables. (Maybe I used it wrong.) Is >there a specific solution? With the command line program: sqlite3 database1 .dump >fileall.sql sqlite3 database2 .dump >>fileall.sql sqlite3 database3 http://www.sqlite.org/sqlite.html >Thanks! Hope this helps. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] data type problem
On Tue, 20 Mar 2007 13:24:17 +0800, you wrote: >if you create a table use following statement (script generated from MS SQL >Server 2000) >CREATE TABLE [XTollData] ( > [DutyID] [char] (32) NOT NULL , > [CarNumber] [char] (10) NULL >); > >SQLite3_Column_decltype will treat DutyID as data type 'char' but not >'char(32)' I don't think [char] (32) in [DutyID] [char] (32) NOT NULL , is valid SQL. Keywords can be quoted with "" or [] to be able to use them as identifiers (i.e. objectnames), but IMHO keywords that have to stay keywords shouldn't be quoted at all. If you choose your columnnames carefully (not contained in the collection of reserved words), the [] could easily be filtered out with sed or awk. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query Issue
Tue, 20 Mar 2007 14:15:52 - Ajay Arora wrote: > [snip] >And it gives me an error saying "e.extract_sequence" does not exist". DROP TABLE IF EXISTS extract_master; CREATE TABLE extract_master ( extract_sequence INTEGER PRIMARY KEY, file_locationTEXT, active integer ); INSERT INTO extract_master VALUES (1,'file1',1); INSERT INTO extract_master VALUES (2,'file1',0); DROP TABLE IF EXISTS extract_parameter; CREATE TABLE extract_parameter ( extract_sequence integer, start_date text, end_date text, portfolio_code integer ); INSERT INTO extract_parameter VALUES (1,'2007-01-01','2007-01-02',1); INSERT INTO extract_parameter VALUES (1,'2007-02-01','2007-02-02',1); INSERT INTO extract_parameter VALUES (2,'2007-03-01','2007-03-02',2); INSERT INTO extract_parameter VALUES (2,'2007-04-01','2007-04-02',2); DROP TABLE IF EXISTS extract_status; CREATE TABLE extract_status ( extract_sequence integer, status integer, datetime text ); INSERT INTO extract_status VALUES (1,1,'2007-01-15 12:00:00'); INSERT INTO extract_status VALUES (2,2,'2007-03-15 12:00:00'); select -- [1] a.extract_sequence,a.file_location,a.active, b.start_date,b.end_date,b.portfolio_code, c.status from extract_master AS a, extract_parameter AS b, (select -- [2] extract_sequence,status from extract_status AS e where datetime = (select -- [3] max(datetime) from extract_status AS s where e.extract_sequence = s.extract_sequence ) ) AS c where a.extract_sequence = b.extract_sequence and b.extract_sequence = c.extract_sequence and c.extract_sequence = a.extract_sequence; results in: 1|file1|1|2007-01-01|2007-01-02|1|1 1|file1|1|2007-02-01|2007-02-02|1|1 2|file1|0|2007-03-01|2007-03-02|2|2 2|file1|0|2007-04-01|2007-04-02|2|2 I don't know if it makes sense, but in sqlite-3.3.13 I didn't get the error you mentioned. Just guessing: In earlier version of sqlite the alias e can't be used in the subselect, in other words: Support for correlated subqueries was added in v3.1.0. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Vista problem on its aggressive "previous version"
On Wed, 25 Apr 2007 22:09:21 +0800, you wrote: >Hi all, > >Is it possible to prevent this problem happens with SQLite DLL itself? >Please refer to >http://b6s.blogspot.com/2007/04/previous-version-ghost-on-vista-can-be.html >for problem description. First of all: I have no hands on experience with Vista... Perhaps you should install the software in directories outside the ones "guarded" by Vista. I think "Program Files" and "Windows" aren't safe anymore for people who want to be in control themselves. >Regards, >/Mike/ Good luck. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recovery After Crash
Hi Asif, On Sun, 17 Jun 2007 12:59:21 +0500, you wrote: >Hi Everybody, > >I have just joined this mailing list as Sqlite looks like a good >software solution to my needs. What I need right now is RE-assurance >of "crash-recovery" that is mentioned on your front page. So, I would >be thankful if you experts would give me an "accurate" and fair >picture of the crash-recovery aspects of SQLite - without any hype. I'm not sure if you would qualify this as hype, but sqlite is used in many end-user products, ranging from operating systems (OS X), webbrowsers (Firefox), embedded systems like PDA's, mp3 players etcetera. These are all enviroments where the users 1) aren't especially careful 2) aren't willing or able to handrecover a database. They even don't know it's there. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Trigger update of multiple columns
On Mon, 18 Jun 2007 23:54:10 +1000, you wrote: >So, my question remains, is it possible to update multiple columns >from a single related row in another table, without having to perform >multiple redundant WHERE clauses? You may want to introduce a sold_products table, one row per product-incarnation, only containing products you actually sold, 1 : n with the sales_products table, which would only contain sale_id and a foreign key pointing to sold_products, not the product properties at the moment of sale. The same row in sold_products could be used by other sales where the exact same product incarnation is sold. sold_products removes the redundancy your solution still has. I wouldn't worry too much about multiple redundant WHERE clauses, because the row would still be in cache and found immediately by product_id. But i agree, what we seem to miss sometimes is an expression to transfer a column list from a subquery to some outer SQL contruct for SETting or comparison purposes. If they existed your trigger action UPDATE sale_products SET buy = (SELECT buy FROM products WHERE products.product_id = NEW.product_id) , sell = (SELECT sell FROM products WHERE products.product_id = NEW.product_id) , desc = (SELECT desc FROM products WHERE products.product_id = NEW.product_id) WHERE ... could be expressed by: SET (buy,sell,desc) = ( SELECT buy,sell,desc FROM products WHERE products.product_id = NEW.product_id ) WHERE ... See also: http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql item 2005.10.06 -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure for versioned data
Hi Sam,
On Wed, 20 Jun 2007 15:33:23 -0400, you wrote:
>Not specific to SQLite, but we're working on an app that needs to keep
>versioned data (i.e., the current values plus all previous values). The
>versioning is integral to the app so it's more than just an audit trail or
>history.
>
>Can anyone share experiences with the database structure for this type of
>requirement or point me to helpful resources?
Joe Celko has a good chapter on temporal data
in his book "SQL for smarties" [1].
Some time ago I found a bash script from Mike Chirico
which maintains a notes database:
http://souptonuts.sourceforge.net/code/n.html
and changed it to my liking as a
demonstration of temporal data [2].
Editing it for publication here may have introduced some
mistakes, testing is left to you ;)
References and quotes:
[1]
Google for ISDN-13: 978-0-12-369379-2
[2]
-- init database
PRAGMA page_size=4096;
PRAGMA cache_size=100;
PRAGMA default_cache_size=100;
-- define schema
BEGIN TRANSACTION;
DROP TABLE IF EXISTS notes;
CREATE TABLE notes (
nkey INTEGER PRIMARY KEY, -- names ROWID
-- category left out in this demo
msg TEXT
);
-- '-12-31 23:59:59' means 'end-of-times'
DROP TABLE IF EXISTS audit;
CREATE TABLE audit (
nkey INTEGER,
-- category left out in this demo
msg TEXT,
timeFrDATETEXT,
timeToDATETEXT DEFAULT '-12-31 23:59:59',
PRIMARY KEY (nkey,timeFr)
);
CREATE TRIGGER audit_insert_notes
AFTER INSERT ON notes FOR EACH ROW
BEGIN
INSERT INTO audit (nkey, msg, timeFr)
VALUES (new.nkey, new.msg, CURRENT_TIMESTAMP);
END;
CREATE TRIGGER audit_update_notes
AFTER UPDATE ON notes FOR EACH ROW
BEGIN
UPDATE audit SET timeTo = CURRENT_TIMESTAMP
WHERE nkey == new.nkey
AND timeTo == '-12-31 23:59:59';
INSERT INTO audit (nkey, msg, timeFr)
VALUES (new.nkey, new.msg, CURRENT_TIMESTAMP);
END;
CREATE TRIGGER audit_delete_notes
AFTER DELETE ON notes FOR EACH ROW
BEGIN
UPDATE audit SET timeTo = CURRENT_TIMESTAMP
WHERE nkey == old.nkey
AND timeTo == '-12-31 23:59:59';
END;
DROP VIEW IF EXISTS sh_audit;
CREATE VIEW sh_audit AS
SELECT *,
strftime('%H:%M:%S',
(strftime('%s',CASE timeTo
WHEN '-12-31 23:59:59' THEN CURRENT_TIMESTAMP
ELSE timeTo
END) - strftime('%s',timeFr)),'unixepoch') AS lifespan
FROM audit
ORDER BY nkey,timeFr;
DROP VIEW IF EXISTS sh_status;
CREATE VIEW sh_status AS
SELECT
COUNT(CASE
WHEN timeTo == '-12-31 23:59:59' THEN 1
ELSE NULL
END) AS active,
COUNT(CASE
WHEN timeTo < CURRENT_TIMESTAMPTHEN 1
ELSE NULL
END) AS deleted
FROM audit
GROUP BY nkey;
COMMIT; -- done definitions
-- test data
INSERT INTO notes (msg) VALUES ('note 1 version 1');
INSERT INTO notes (msg) VALUES ('note 2 version 1');
UPDATE notes SET msg = 'note 1 version 2' WHERE nkey == 1;
UPDATE notes SET msg = 'note 2 version 2' WHERE nkey == 2;
UPDATE notes SET msg = 'note 1 version 3' WHERE nkey == 1;
-- queries
SELECT * FROM sh_audit;
SELECT * FROM sh_status;
SELECT 'notes';
SELECT * FROM notes;
SELECT 'audit';
SELECT * FROM audit;
SELECT 'audit with timediff';
SELECT * FROM sh_audit;
>Thanks,
>
>Sam
>
I hope this helps.
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Question about triggers
On Wed, 27 Jun 2007 20:42:18 -0400, you wrote: >Hello all: >I was reading through a couple of sqlite tutorials and I noticed >examples of timestamping database entries by using triggers. I wanted >to ask people's opinion about using triggers to timestamp records in a >database. > >Which is 'better': using a trigger to timestamp records or explicity >adding the timestamp in the sql statement? > >In my own analysis (brief < 5 min), I have thought of the following >pro's and con's. > >1) Faster to do the timestamp in the sql statement, since it does not >require activation of the trigger. >2) Query looks 'cleaner', not having an explicit datetime() inside >3) Can switch the timestamp between UTC and localtime by modifying the >trigger as opposed to changing and recompiling the application. 4) Triggers can't be forgotten, so the stamping will be more consistent, much like constraints. As a side note, I seem to remember all sqlite timestamps within a transaction have the same value: the time at BEGIN TRANSACTION. To me that is the most important reason to have the DBMS do the timestamping, not the host language. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compiling for Classic VB?
On Sat, 14 Jul 2007 02:42:34 +0200, you wrote: >PS : BTW, is there a plan to add a forum on the www.sqlite.org site to >replace the mailing-list? To be honest: I hope not. For me, a personal mail archive is much better accessible than any forum. If you prefer to browse the mailing list using a web interface, http://www.mail-archive.com/sqlite-users@sqlite.org/ might suit your needs. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How do I unsubscribe?
On Tue, 24 Jul 2007 11:59:28 -0400, you wrote: > How do I unsubscribe? >Thanks. > > >NOTICE: If received in error, please destroy and notify sender. Sender does >not intend to waive confidentiality or privilege. Use of this email is >prohibited when received in error. Exactly the way it says in about every message: - To unsubscribe, send email to [EMAIL PROTECTED] ----- -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite.org needs online forms
On Fri, 3 Aug 2007 09:35:49 -0400, you wrote: >Sounds like Wikipedia. I understand that Wikipedia's software is Open >Source and available. Do you know of other Wiki servers that would suffice >or be more on target? There are several wiki software packages avaialble. The wiki server behind http://www.sqlite.org/cvstrac/wiki is more than adequate for its purpose. To answer some of your other questions: The timeline http://www.sqlite.org/cvstrac/timeline and bugtracker reports http://www.sqlite.org/cvstrac/reportlist are informative the way they are. I can't see Sourceforge or such would improve this. To me, the (this) mailing list sqlite-users@sqlite.org and its archives are better than any forum. It's easy enough to instruct good anti-spam software to respect any mailing lists one is subscribed to. And just like Andrew I prefer to have all info pushed to me. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
Hi Chris, On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: >On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: > >>Chris Peachment wrote: >>> I have a database with more than 200,000 records in the >>> core table. An update table of similar record count contains >>> a proper subset of the core table columns. >>> >>> I'm looking for a fast method of merging the values in the >>> two tables such that : >>> >>> 1. core table columns are updated, and >>> 2. non-existent core records are inserted from the update table. >>> >>Will INSERT OR REPLACE do what you want? > > >>Gerry > > >Regrettably no. When an existing core record is found then it >is deleted before the insert. That means that all columns are >given new values and not just the ones to be updated. That is exactly what INSERT OR REPLACE does. http://www.sqlite.org/lang_insert.html http://www.sqlite.org/lang_conflict.html >Chris -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
On Thu, 23 Aug 2007 14:15:00 -0400, you wrote: >On Thu, 23 Aug 2007 18:58:32 +0200, Kees Nuyt wrote: > >>Hi Chris, > >>On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: > >>>On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: >>> >>>>Will INSERT OR REPLACE do what you want? >>> >>>>Gerry >>> >>>Regrettably no. When an existing core record is found then it >>>is deleted before the insert. That means that all columns are >>>given new values and not just the ones to be updated. > >>That is exactly what INSERT OR REPLACE does. > >>http://www.sqlite.org/lang_insert.html >>http://www.sqlite.org/lang_conflict.html > > >Sorry for the confusion I introduced. I know the behaviour >of INSERT OR REPLACE is as-described, and that is NOT >what I want. I need to keep the non-updated columns. > >Chris Oops, I obviously misread your statement. Just a suggestion (no time to try it myself): Perhaps a BEFORE INSERT trigger on Core can help, triggered by an INSERT ... SELECT ... FROM UpdateTable? I'm not sure if it would work and how fast it would be. Good luck! -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Skype client using SQLite?
On Tue, 28 Aug 2007 15:13:47 +, you wrote: >In reference to > > http://www.sqlite.org/cvstrac/tktview?tn=2592 > >This is the first public indication we have had that >Skype is using SQLite in their windows clients. However, >the person who wrote the ticket seems to be a bit confused. >Can any able hackers in the SQLite community confirm that >the Skype windows client is using SQLite? It would be >nice to add them to the page of high-profile users. I think he is confused indeed. The use of C:\Windows\Temp\ as temp folder, and the problems he has to get voice working in not only Skype but also other messenger programs seem to indicate an old version of Windows (95 or 98, maybe ME). Newer versions (WinNT family) would use a folder within a user profile. Assuming an old Windows installation we might also assume an old version of the virusscanner that still uses a version of SQLite that prefixes its tempfiles with sqlite instead of etilqs. My own Skype installation doesn't show any trace of SQLite. Either they don't use it, or they've hidden it very well. CyberLink seems to use SQLite for its PowerDVD and/or PowerProducer programs to store references. Table names: ATSC_Frequency ATSC_FrequencyTable Country CountryToATSC_FrequencyTable CountryToDVBT_FrequencyTable DVBS_Channel DVBS_Frequency DVBS_Region DVBS_Satellite DVBT_Frequency DVBT_FrequencyTable DVBT_FrequencyTableToFrequency DVB_IPService Version Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Skype client using SQLite?
On Tue, 28 Aug 2007 18:44:50 +0200, you wrote: >On Tue, 28 Aug 2007 15:13:47 +, you wrote: > >>In reference to >> >> http://www.sqlite.org/cvstrac/tktview?tn=2592 >> >>This is the first public indication we have had that >>Skype is using SQLite in their windows clients. However, >>the person who wrote the ticket seems to be a bit confused. >>Can any able hackers in the SQLite community confirm that >>the Skype windows client is using SQLite? It would be >>nice to add them to the page of high-profile users. > [snip] > My own Skype installation doesn't show any trace of SQLite. > Either they don't use it, or they've hidden it very well. Oops, found it: C:\Users\\AppData\Roaming\Skype\\dyncontent\bundle.dat -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] CURRENT_TIMESTAMP value in single transaction
Hi Nick, On Tue, 28 Aug 2007 17:39:16 +0100, you wrote: > When enclosed in a single transaction, would inserting > many rows into a table using the special default keyword > 'CURRENT_TIMESTAMP' result in all of the rows > guaranteeing the same timestamp value? Did you try? I did. Surprisingly, it doesn't give the same value in every row. Perhaps it should. Joe Celko thinks it should. (SQL for Smarties, third edition, 2005 Elsevier; paragraph 4.2.3 Handling Timestamps). > If not, is there a recommended way to assign a unique > value to a collection of inserts in a single transaction > generated from a trigger? I'm sorry, I have no usable ideas at the moment. Your problem suggests the timestamp will be used as a foreign key. In such cases I usually use a natural key (i.e. propagate the primary key of the referred table) or generate a synthetic key for both in the application. > Thanks in advance > Nick Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to generate Unique ID?
Hi Phani, On Thu, 30 Aug 2007 16:51:27 +0530, you wrote: >Simon, > Yeah you can term the problem like that. Can't I use the >function which is assigning a unique id for INTEGER PRIMARY KEY column >inside sql? If yes, how to use it? > >Regards, >Phani SQLite will do the job for you, it's all in the documentation. http://www.sqlite.org/lang_createtable.html : Specifying a PRIMARY KEY normally just creates a UNIQUE index on the corresponding columns. [snip] Additional detail on automatic B-Tree key generation is available separately. -> http://www.sqlite.org/autoinc.html : When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. To specify a ROWID manually [snip] If no ROWID is specified on the insert, an appropriate ROWID is created [snip] Note: this solution is not standard SQL and not portable. For more or less protable solution you would have to use smart trigger code. Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance tuning for Insert and select operations
On Fri, 31 Aug 2007 09:34:18 +0530, you wrote: >Hi, > >I am using SQLite 3_3_17with the default configuration of SQLite. >Using Windows XP C++ API. >50,000 inserts into Table (with primary key and no other index as single >transaction commit) is taking 4.609000 sec. >50,000 selects from Table ( having 1,00,000 rec with primary key and no >other index) , query using primary key is taking 8.751000 sec. >I have to achieve insertion bench mark of nearly 50,000 insertions in 1.5 to >2 sec. >I have to achieve selection bench mark of nearly 50,000 selections in 1.5 to >2 sec. >Please tell me if it is possible to tune Sqlite to achieve this performance. > >Regards, >Ramana In general: 1) Optimize your schema and queries. 2) Look at what PRAGMA can do for you, and apply them in the correct order. http://www.sqlite.org/pragma.html 3) Before loading, sort your input in key order. 4) Tune your operating system and file system. 5) Adjust hardware. Good luck -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] remote access to sqlite db?
On Sun, 2 Sep 2007 23:23:43 -0400, you wrote: >Hi, > >Does sqlite offer the ability to connect to a sqlite db file on a >remote machine? I've been using it locally for awhile and it's great. >Wanted to see if it could be used remotely for some simple tasks. It does, but there are restrictions: http://www.sqlite.org/whentouse.html For very low concurrency (one user at a time), it shouldn't be a problem, but you will notice loss of speed. There are also a few client/server drivers for SQLite: http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork http://www.sqlite.org/contrib sqlite-networked >Thanks, >Mark Hope this helps. Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] why a VIEW doesn't contain a ROWID
Hi Lokesh,
On Mon, 3 Sep 2007 15:30:10 +0530, you wrote:
> This I know, but the thing is, I want the ROWID
> in VIEW to be sequential even after a SELECT with
> some condition has been executed, ie., from 1 to n.
> Just like in normal table.
> In your case it is not like that.
If you delete rows from a table the tables' rowid isn't
consecutive anymore:
CREATE TABLE testTbl(
t_id INTEGER PRIMARY KEY,
t_name TEXT
);
INSERT INTO testTbl VALUES( 1, 'd1' );
INSERT INTO testTbl VALUES( 2, 'd2' );
INSERT INTO testTbl VALUES( 3, 'd3' );
INSERT INTO testTbl VALUES( 4, 'd4' );
SELECT * FROM testTbl;
1|d1
2|d2
3|d3
4|d4
DELETE FROM testTbl WHERE t_id=2;
SELECT * FROM testTbl;
1|d1
3|d3
4|d4
> By the way, what I mean to say is, why
> don't we have default ROWID in >VIEW
> like as in normal TABLE.
Because a view isn't a table.
By the way, the concept of rowid is not in the SQL standard.
It is a physical property (the B-Tree key) which rows happen to
have when they are stored the SQLite way. It has no other
meaning. A member of a set doesn't have an ordinal number in
relational theory.
Richard Hipp made rowid visible because some tight embedded
applications can be speeded up nicely by using it.
Any code which uses the rowid is not portable, though.
The number of a row in a view is its order of its occurence.
The first row has number 1
The second row has number 2
etc.
It is easy to materialize that number in any language you will
use around your SQL, even in a shell:
sqlite3 your.db "select * from testTbl;" | \
awk -v OFS='|' '{print NR,$0}'
1|1|d1
2|3|d3
3|4|d4
note: \ is linewrap
Regards,
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On Mon, 3 Sep 2007 18:29:55 +0100, you wrote: >This made me to remember there was a bug some time ago about the >rounding algorithm (but can't remember at what version it was fixed), >so I just tested it. > >"official" amalgamated sqlite 3.4.0 downloaded from the site some time ago: > >SQLite version 3.4.0 >Enter ".help" for instructions >sqlite> select round(98926650.5, 1) ; >98926650.5 >sqlite> select round(85227887.01, 1) ; >85227887.0 >sqlite> select round(85227887.01, 2) ; >85227887.01 >sqlite> select round(98926650.50001, 1) ; >98926650.5 > >Linux [K]Ubuntu 7.04 (feisty) sqlite3 package 3.3.13-0ubuntu1: > >SQLite version 3.3.13 >Enter ".help" for instructions >sqlite> select round(98926650.5, 1); >98926650.5 >sqlite> select round(85227887.01, 1); >85227887.0 >sqlite> select round(85227887.01, 2); >85227887.01 >sqlite> select round(98926650.50001, 1) ; >98926650.5 > >$ uname -a >Linux ubuno 2.6.20-16-generic #2 SMP Thu Jun 7 20:19:32 UTC 2007 i686 GNU/Linux > > >So it seems SQLite is already doing the right job. >Maybe some OS specific error? Wasn't there some discussion earlier >about the Microsoft compiler not using the full double precision by >default? > > >Regards, >~Nuno Lucas I found something in a post by Joe Wilson. Message-ID: <[EMAIL PROTECTED]> Date: Sun, 10 Jun 2007 15:55:32 -0700 (PDT) From: Joe Wilson <[EMAIL PROTECTED]> He found: http://support.microsoft.com/kb/102555 Microsoft Visual C++ runtime library provides default floating-point exception handling and includes functions such as _controlfp for determining and adjusting the floating-point hardware's rounding, precision control, and exception handling behavior. More info on fp:precise and _controlfp: http://msdn2.microsoft.com/en-us/library/aa289157(vs.71).aspx I'm not sure this is related. Out of curiousness I tried it myself on Microsoft Windows [Version 6.0.6000, aka vista] SQLite version 3.4.2 sqlite> select round(98926650.50001, 1); 98926650.501 sqlite> select round(98926650.50001 -0.1, 1); 98926650.501 sqlite> select round(98926650.50001 -0.0001, 1); 98926650.501 sqlite> select round(98926650.50001 -0.001, 1); 98926650.501 sqlite> select round(98926650.50001 -0.01, 1); 98926650.501 sqlite> select round(98926650.50001 -0.1, 1); 98926650.4 sqlite> Oh, well, 9 digits of accuracy is way more than most measurements we can do in daily life. For money, use integers and express in cents / centimes or something. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] BigNameUsers: Nokia using SQLite in PCsuite
Hi folks, Nokia uses SQLite on Windows to exchange data between PC and a cellphone using what they call Nokia PC Suite. Not very surprising, as Symbian is the OS on Mokia high end phones, but SQLite is also used in exchanges with its 6233 model, which doesn't run Symbian, but Nokia OS, with Series 40 UI. I'm not sure if SQLite is used in the cellphone itself, I only found a SQLite3 database in a PC disk directory used for exchanges. Schema: PRAGMA page_size=1024; PRAGMA default_cache_size=2000; PRAGMA encoding=UTF-8; PRAGMA legacy_file_format=1; CREATE TABLE contact_general ( uid PRIMARY KEY, name TEXT, first_name TEXT, middle_name TEXT, last_name TEXT, nickname TEXT, formal_name TEXT, versit_object BLOB, versit_length INTEGER ); CREATE TABLE contact_numbers ( gen_info_uid INTEGER, number_text TEXT, number_type TEXT ); CREATE TABLE sms_folders ( sms_folder_name TEXT, sms_view_name TEXT, sms_options INTEGER ); CREATE TABLE sms_messages ( sms_uid PRIMARY KEY, sms_info_field INTEGER, sms_data_length INTEGER, sms_data BLOB, sms_date_year INTEGER, sms_date_month INTEGER, sms_date_day INTEGER, sms_date_hour INTEGER, sms_date_minute INTEGER, sms_date_second INTEGER, sms_date_timezone INTEGER, sms_date_bias INTEGER, sms_folder TEXT, sms_address_1 TEXT, sms_address_1_type INTEGER, sms_address_2 TEXT, sms_address_2_type INTEGER ); CREATE VIEW sms_predefinbox_view AS SELECT * FROM sms_messages WHERE sms_folder='predefinbox'; Pictures: http://www.forum.nokia.com/devices/pics/6233_main.jpg http://www.nokia.com/search/images/logo_nokia_115_40.gif (I didn't find a real good one) -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] why a VIEW doesn't contain a ROWID
On Mon, 03 Sep 2007 18:05:05 -0300, you wrote:
>Hi Kees,
>
>He is telling about the Rowid the unique number that represents each row
>in the table, not about a table column named "ID" or anything else, or
>the primary key of the table.
>
>[]'s,
>
>Marco Antonio Abreu
>IT Quality Systems
>[EMAIL PROTECTED]
>http://www.itquality.com.br
You are right, but if a column is defined as INTEGER PRIMARY KEY
it acts as an alias for the (physical) ROWID, so it acually
describes the same case.
Definition:
http://www.sqlite.org/lang_createtable.html says:
Specifying a PRIMARY KEY normally just creates a UNIQUE index on
the corresponding columns. However, if primary key is on a
single column that has datatype INTEGER, then that column is
used internally as the actual key of the B-Tree for the table.
This means that the column may only hold unique integer values.
[...] If a table does not have an INTEGER PRIMARY KEY column,
then the B-Tree key will be a automatically generated integer.
The B-Tree key for a row can always be accessed using one of the
special names "ROWID", "OID", or "_ROWID_". This is true
regardless of whether or not there is an INTEGER PRIMARY KEY.
[...]
Proof:
CREATE TABLE testTbl(
t_name TEXT
);
INSERT INTO testTbl VALUES('d1');
INSERT INTO testTbl VALUES('d2');
INSERT INTO testTbl VALUES('d3');
INSERT INTO testTbl VALUES('d4');
SELECT ROWID,t_name FROM testTbl;
1|d1
2|d2
3|d3
4|d4
DELETE FROM testTbl WHERE t_name='d2';
SELECT ROWID,t_name FROM testTbl;
1|d1
3|d3
4|d4
same picture..
Regards,
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Eliminate duplicate entries
On Tue, 04 Sep 2007 07:53:08 -0600, you wrote: >I have an application that is inserting a record every second. There >are thousands of periods from a few seconds to hours long where the data >in all the columns is identical thus causing hundreds of thousands of >duplicate rows. > >Is there a way to set up the INSERT statement to allow the insertion of >a new row of data, where one or more columns has data != to the previous >insertion, but ignore or skip the insertion of a duplicate row? > >Scott Perhaps: Specify all columns in the primary key, or define a separate table constraint UNIQUE (on,all,columns) ON CONFLICT IGNORE. The insert will fail. It depends on the conflict-clause what happens. Or you can use INSERT ... ON CONFLICT IGNORE. http://www.sqlite.org/lang_conflict.html -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Eliminate duplicate entries
On Tue, 04 Sep 2007 09:20:28 -0700, Gerry wrote: >Kees Nuyt wrote: >> On Tue, 04 Sep 2007 07:53:08 -0600, Scott wrote: >> >>> I have an application that is inserting a record every second. There >>> are thousands of periods from a few seconds to hours long where the data >>> in all the columns is identical thus causing hundreds of thousands of >>> duplicate rows. >>> >>> Is there a way to set up the INSERT statement to allow the insertion of >>> a new row of data, where one or more columns has data != to the previous >>> insertion, but ignore or skip the insertion of a duplicate row? >>> >>> Scott >> >> Perhaps: Specify all columns in the primary key, or define a >> separate table constraint UNIQUE (on,all,columns) ON CONFLICT >> IGNORE. >> The insert will fail. It depends on the conflict-clause what >> happens. Or you can use INSERT ... ON CONFLICT IGNORE. >> >> http://www.sqlite.org/lang_conflict.html >> >This may do what is needed, or it may not. > >This would skip an insertion that matched any previous data, even from >days earlier with many samples of different data in between. If the data >contents don't allow that, your scheme will work. > >Otherwise I think a TRIGGER could do what is needed. > >HTH, > >Gerry You are right. Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ColType lost
On Wed, 5 Sep 2007 10:54:55 +0200, Andre wrote: > I use sqlite3_column_type so the SQLITE_NULL > may actually be on a different [row] than the > one that actually has data, I will keep > investigating and report back, I might have > to use ... sqlite3_column_type() on every row you sqlite3_step() into. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] New Operator Support
On Fri, 07 Sep 2007 17:16:39 +0800, Ragha wrote: >Hi, > >Just to get more hands on Sqlite i want to >write a custom operator. Pls suggest how i can do it. > >For example >select * from tablex where column1 ~ '123'; > >I want implement it similar to '='. Can anyone help me >what all steps,files i need to change? > >regards >ragha This should get you started: http://www.sqlite.org/docs.html more specifically: http://www.sqlite.org/arch.html http://www.sqlite.org/vdbe.html http://www.sqlite.org/opcode.html http://www.hwaci.com/sw/lemon/ http://www.sqlite.org/datatype3.html Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite in memory
Hi Alex, On Wed, 12 Sep 2007 12:19:44 +0200, you wrote: > I have 3 questions regarding sqlite database loaded/used whilst in memory: > > 1. How can an sqlite database file (example file1.db) be >loaded in memory? > (Is this the answer?: > sqlite3.exe file1.db) sqlite3 file1.db .dump | sqlite3 :memory: The default database (no name specified) is in memory, so: sqlite3 file1.db .dump | sqlite3 would do the same. > 2. How can the in-memory sqlite database be accessed >by multiple applications? No. The only way to do that would be to construct a 'server wrapper' around sqlite and have applications (clients) connect to that server. It has been done before, for example, there is an ODBC wrapper. http://www.sqlite.org/cvstrac/wiki >3. Can multiple threads or applications access simultaneously > the same in-memory sqlite database? Not without the server mechanism. The performance gain of a :memory: database is limited, one of the reasons is that most operating systems will cache the database file in memory anyway. In general: when you really need a DB server, don't use SQLite. http://www.sqlite.org/whentouse.html >Thank you. Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multi column select
On Wed, 12 Sep 2007 18:39:02 +0800, you wrote: >Hi, > >Can anyone explain how does the below sql statmenent work, > > select * from tbl1 where a=xx and b=yy; when a is > indexed and b is indexed seperately? I tried explain > could not make out. > Does it select all records with t=111 and then do a > search for b=222 with in that set or other way? Perhaps you will find an explanation here: http://www.sqlite.org/optoverview.html Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Converting sqlite2 to sqlite3
Hi Bernie, On Wed, 12 Sep 2007 07:59:25 -0400, you wrote: >I'm just restarting a long dormant project that uses SQLite [also >converting it from being CGI-driven to being a self-contained Perl/Tk >app]. I've discovered that my old database, which is likely sqlite2, >won't open in the world of sqlite3 [duh!!]. I looked through the wiki >and around the sqlite.org website and I hoped to find a 2->3 conversion >tool, but I didn't see one. > >Any recommendations on how to effect the conversion. I see that >there's a command-line tool for reading sqlite 2 DBs, so I guess I >could do some sort of dump/export or something like that with that and >then use an sqlite3 command line tool to import it. ??? > >Thanks! /bernie\ sqlite your.db2 .dump | sqlite3 your.db3 The download page lists both versions of the command line tool. http://www.sqlite.org/download.html Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite insertion performance
On Fri, 14 Sep 2007 23:20:53 +0300, you wrote: > >Dear All, > >I have been struggling with the performance of insertion in sqlite. > >Here we have a very simple case : > >A table with an integer autoincrement primary key and a text field that is >unique. > >CREATE TABLE my (id PRIMARY KEY, url); > >CREATE UNIQUE INDEX myurl ON my(url); > > >My application requires inserting up to 10 million records in batches of 20 >thousand records. > >I use : >A. PRAGMA SYNCHRONOUS = OFF >B. Prepared Statement >C. setAutoCommit(false), then to true at the end of the batch. > >Using the above, the insertion starts off at a good speed, then drastically >slows down as more records are inserted. > >It goes like this : > >The first four inserstions (first 4*20K -> 60K records) >0took786 >1took944 >2took1001 >3took1303 > >After the first 1 Million records >50took2560 >51took1921 >55took1900 >53took3990 > >After the 2nd million records >2took22393 >3took16789 >4took29419 >5took13993 > >By the end of the the 3rd million records the insertion time goes up to 30 >seconds per 20K records. > >My app is running from a java code and I am using SqliteJDBC 0.37 (the latest) >on Sun JDK 1.6.0_02 on CentOS 5.0 4GB Ram, two duo core cpu server with >raid10 sata-II harddisk. > > >I know I might be stretching sqlite far beyond its limits, I just want to >verify that there is nothing more that can be done to make a case for sqlite >in this scenario. I am not sure whats the best next thing to do either. > >Your feedback and input will be highly appreciated, > >- Kefah. Most probably the UNIQUE INDEX on the TEXT column is the culprit. My first try would be to create and fill the table first, and create the UNIQUE INDEX on the TEXT column afterwards. The second suggestion would be to INSERT the rows in sorted order, the sort key being the TEXT column. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite insertion performance
Hi Kefah, On Sat, 15 Sep 2007 04:43:46 +0300, you wrote: >Thanks Kees, > >In fact using integer instead of string gives very similar results. > >Dropping the unique index constraint and then creating it again when needed >sounds interesting, as insertion without the unique index is satisfactory and >constact over time. Thanks for the feedback. >I tried this, but got a trivial question : >When creating the unique index, sqlite gives me : >SQL error: indexed columns are not unique > >What should be done here? Apparently the data in the text column is not unique. That is the disadvantage of building the index after the insertions: the database can't exercise the contraints on your data, so you would have to do that yourself, for example by a sort --unique step. My second suggestion for speeding things was sorting the input data, so now you have two reasons for a sort. Such a data cleaning step will take considerable time, so time gained in inserting may be lost again in preprocessing. It might be better to use the database constraints, and live with the slow insertions. Your benchmarks will tell you what's best. >Thanks again, >- Kefah. Good luck. >On Saturday 15 September 2007 00:25:03 Kees Nuyt wrote: >> On Fri, 14 Sep 2007 23:20:53 +0300, you wrote: >> >Dear All, >> > >> >I have been struggling with the performance of insertion in sqlite. >> > >> >Here we have a very simple case : >> > >> >A table with an integer autoincrement primary key and a text field that is >> >unique. >> > >> >CREATE TABLE my (id PRIMARY KEY, url); >> > >> >CREATE UNIQUE INDEX myurl ON my(url); >> > >> > >> >My application requires inserting up to 10 million records in batches of >> > 20 thousand records. >> > >> >I use : >> >A. PRAGMA SYNCHRONOUS = OFF >> >B. Prepared Statement >> >C. setAutoCommit(false), then to true at the end of the batch. >> > >> >Using the above, the insertion starts off at a good speed, then >> > drastically slows down as more records are inserted. >> > >> >It goes like this : >> > >> >The first four inserstions (first 4*20K -> 60K records) >> >0took786 >> >1took944 >> >2took1001 >> >3took1303 >> > >> >After the first 1 Million records >> >50took2560 >> >51took1921 >> >55took1900 >> >53took3990 >> > >> >After the 2nd million records >> >2took22393 >> >3took16789 >> >4took29419 >> >5took13993 >> > >> >By the end of the the 3rd million records the insertion time goes up to 30 >> >seconds per 20K records. >> > >> >My app is running from a java code and I am using SqliteJDBC 0.37 (the >> > latest) on Sun JDK 1.6.0_02 on CentOS 5.0 4GB Ram, two duo core cpu >> > server with raid10 sata-II harddisk. >> > >> > >> >I know I might be stretching sqlite far beyond its limits, I just want to >> >verify that there is nothing more that can be done to make a case for >> > sqlite in this scenario. I am not sure whats the best next thing to do >> > either. >> > >> >Your feedback and input will be highly appreciated, >> > >> >- Kefah. >> >> Most probably the UNIQUE INDEX on the TEXT column is the >> culprit. >> >> My first try would be to create and fill the table first, and >> create the UNIQUE INDEX on the TEXT column afterwards. >> >> The second suggestion would be to INSERT the rows in sorted >> order, the sort key being the TEXT column. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Primary Keys of a table
On Tue, 18 Sep 2007 15:18:27 +0530, you wrote: >All, > > > >Is there any API in sqlite3 which will provide the Primary keys of the table >and also the Non-primary keys of the table? Here is something to start with: SQLite v3.4.2 CREATE TABLE persons( person_id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, dtstamp DATETIME DEFAULT CURRENT_TIMESTAMP); CREATE INDEX idx_persons_name ON persons(name); PRAGMA table_info(persons); cid name type notnull dflt_value pk --- -- --- -- -- 0 person_id INTEGER 99 1 1 name TEXT 99 0 2 dtstampDATETIME 0 CURRENT_TI 0 -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBIException: The name 'bdate' is not a valid index.
On Thu, 20 Sep 2007 00:53:01 -0400, you wrote: > >Greetings! > >I have this db containing this table: > >CREATE TABLE LSOpenJobs >( > id integer primary key, ProjID integer, subProjID integer, parent, >children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, >pmuk, lang, vendor, vEmail, invoice, ProjFund, PMTime, A_No, wDir, BiliDir, >TMDir, DeliveryDir, paid, paidDate, notes, status, pages, ta >); > >I am trying to make a report and I am calling this select statement, > > select ProjID, cust, proj, bdate, ddate, edate, sum(invoice), sum(ProjFund) > from LSOpenJobs where ProjID = 423 AND PClass!='Quote' group by ProjID; > >There are more than one record where ProjID = 423 and PClass != "Quote". >But when I run the program, I get, > >DBIException: The name 'invoice' is not a valid index. [...] >Any help would be greatly appreciated. Does it work when you use those queries in the sqlite3 command line utility? My guess is that there's something wrong with the quoting of the query string you offer to your wrapper. Another guess is that try to sum strings, bcz the colums aren't defined as numeric in the table definition. >thanks, > >josé Hope this helps, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple databases
On Thu, 20 Sep 2007 02:03:59 -0700 (PDT), you wrote: > >Hello again! >I was really surprised and relieved to see that so many people replied to my >question! I really thank you all! >I would love to come to the conference but i am from Greece. >I am going to explain in details what i am trying to do. >I am indeed using the network simulator 2 in order to simulate a wireless >network of nodes. I want each node to have its own database which will NOT >be a copy of a local db. Each database will be different and independent for >each node. This is and my basic worry. Can i do that? >Additionally, each node will send some messages to other nodes. These >messages will trigger some 'actions' to the local dbs. Let's say that one >node nA wants to make a query to the db of the node nB. Then nA sends a >serialized message (which contain the query) to the node nB and then node nB >deserialises the received message and 'reads' the query and access its >database. This is what i want to simulate. So, can this be done? >Can i attach so many different dbs as the nodes are? >I would prefer to do it through c++ and not through Tcl. >Thank you so much! Assuming each node is an object (class instance) in the simulation program it should be possible to instantiate a database connection (open) in the constructor of the node class and cose it in the destructor. The constructor should check if the database has been initialized before, and if not, define a proper schema. The class methods for receiving messages could contain sqlite3 calls that use the database in some way. You have to take care that every database should have a unique filename, and that a specific node in the simulated network uses the same name all the time. I hope this helps, good luck. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Formatting numbers
On Thu, 20 Sep 2007 17:41:54 +1000, you wrote: >Hi All, > >What's the best way to format a number with a fixed >number of decimal places? > >For instance, if I have an Amount that I want to >format as dollars and cents, I'm currently using: > >begin immediate; >create temporary table Invoice( Amount real ); >insert into Invoice values( 123.4 ); >select '$' || cast( Amount as integer ) || '.' > || substr( cast( Amount * 100 + 100 as integer ), -2, 2 ) >from Invoice; >rollback; > >which gives: > >$123.40 > >Is there a better way? I can't see any number formatting function in >SQLite's repertoire. Round comes closest, but is not exacly what you need. select '$' || round( Amount ,2) from Invoice; Two remarks: Formatting and presentation is usually considered a task of the host language, not of SQL. Valuta are best stored in integers (as cents). >Thanks, >Tom -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Precompiled 2.x for Windows?
On Thu, 20 Sep 2007 17:09:27 +, you wrote: > Would anyone happen to have a precompiled DLL > for Windows of the latest 2.x version of SQLite? > I don't have the facilities to make one myself > where I'm at right now, but I need one to test > out a problem with an application I'm trying > to support. > Thank you to anyone who might be able to provide this for me. It's on the bottom of the download page: http://www.sqlite.org/download.html -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_open_v2 and SQLITE_OPEN_READONLY
On Thu, 20 Sep 2007 13:57:58 -0400, Liam wrote:
> It would be nice if the documentation gave a
> brief indication when a feature is added
> ("new in 3.5.0" unobtrusively somewhere).
Like in http://www.sqlite.org/34to35.html ?
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Precompiled 2.x for Windows?
On Thu, 20 Sep 2007 20:42:30 +, you wrote: > Where? sqlite-2_8_17.zip is just an executable, > and the other zip for Windows is just the source. > As it stands, however, I was able to recompile > the DLL after all and it didn't make a difference. Oh, sorry, I should have checked. The link isn't on the page anymore, but (as with many old versions) the download is still available: http://www.sqlite.org/sqlitedll-2_8_17.zip I'm glad you managed to compile it by yourself. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite occasionally becomes CPU greedy
On Mon, 24 Sep 2007 07:13:51 -0700, Gururaja Nittur wrote: >Sqlite experts, > >I am running Sqlite version 3.4.1. I ran some performance tests and >observed that sqlite sometimes consumes more CPU than normal (I am >using same dataset, all inserts and recreating new database for each >test). Tried running oprofiile and got the following result. The >function 'syncJournal' is taking around 60% of total Sqlite's CPU >usage. > > 1. Is this normal behaviour? If not, is there a known fix for this? Do you use transactions? If not, every INSERT or UPDATE is a separate transaction, causing a lot of overhead because the journal is flushed for every transaction. > 2. Are there any tunables that can be used to ease the issue? Several pragma's are available, the documentation is quite clear: http://www.sqlite.org/pragma.html Also read: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations >PS: My database is pretty small. >I am setting max_page_count=896 and >page_size = 512 (Max database size = 3.5MB). I don't understand that one. 512 Bytes * 896 Pages would be 458752 Bytes. I think you would need 7168 pages of 512 bytes to accomodate 3.5 MByte. Ideally, the page_size should equal the allocation unit (for Window: the cluster size of the formatted disk). Also, if your rows can be large, a too small page_size would cause overflow pages, and thus additional overhead. >Thanks in advance. I hope this helps. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite occasionally becomes CPU greedy
On Mon, 24 Sep 2007 10:35:51 -0700, you wrote: >Kees Nuyt wrote: >> On Mon, 24 Sep 2007 07:13:51 -0700, Gururaja Nittur wrote: >> >> >>> Sqlite experts, >>> >>> I am running Sqlite version 3.4.1. I ran some performance tests and >>> observed that sqlite sometimes consumes more CPU than normal (I am >>> using same dataset, all inserts and recreating new database for each >>> test). Tried running oprofiile and got the following result. The >>> function 'syncJournal' is taking around 60% of total Sqlite's CPU >>> usage. >>> >>> 1. Is this normal behaviour? If not, is there a known fix for this? >>> >> >> Do you use transactions? If not, every INSERT or UPDATE is a >> separate transaction, causing a lot of overhead because the >> journal is flushed for every transaction. >> > >I'm confused. With a bunch of INSERTs per transaction, the CPU usage can >be high. But with just one per transaction things will be I/O bound, and >I would expect low CPU usage. That's true. The thing is, Guru isn't worried about the total CPU usage, but about the relative amount of CPU time that is spent in synching the journal (60%), relative to the total CPU time in SQLite (100%). >Gerry -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite occasionally becomes CPU greedy
On Mon, 24 Sep 2007 21:36:14 -0700, Gururaja Nittur wrote:
>On 9/24/07, Kees Nuyt <[EMAIL PROTECTED]> wrote:
>>
>> On Mon, 24 Sep 2007 10:35:51 -0700, Gerry Snyder wrote:
[big snip]
>> >I'm confused. With a bunch of INSERTs per transaction, the CPU usage can
>> >be high. But with just one per transaction things will be I/O bound, and
>> >I would expect low CPU usage.
>>
>> That's true. The thing is, Guru isn't worried about the total
>> CPU usage, but about the relative amount of CPU time that is
>> spent in synching the journal (60%), relative to the total CPU
>> time in SQLite (100%).
>
>
>Yes! you are right. The overall CPU is not that high. I am seeing
>spikes in sqlite CPU usage and not getting consistent results. So
>started profiling. It may be due to transaction size as well.
>
>I tried to understand syncJournal code a little bit. It is pretty
>evident that the following loop in pager.cc is consuming lot of CPU
>cycles (it iterates upto SQLITE_DEFAULT_CACHE_SIZE, i.e, 2000 times
>per transaction).
>
> 2716 22.6768 :for(pPg=pPager->pAll; pPg; pPg=pPg->pNextAll){
> 79 0.6596 : pPg->needSync = 0;
> :}
>
>One thing I didn't understand is, why we need to traverse through all
>pages and update needSync flag. Can't we keep a list of dirty pages
>and clear needSync only in those pages?
First of all: I'm not an expert in SQLite internals, so I can
only speak in general terms.
This pPg=pPg->pNextAll loop might be more efficient than
maintaining another pointer chain for a list of dirty pages.
Compared to the time spent on I/O waits this loop will be quite
fast for a few thousand pages anyway: The code path for a single
I/O (one database page) typically is in the order of 5000 to
1 CPU instructions, depending on the OS of course, and it
will also cause a few context switches. This loop is in the
order of 10 instructions per page.
By the way, this is one more reason to use a larger page size.
Transaction size is relevant here, the bigger the better, but
not bigger than the cache can hold.
> Also, what would be the effect of reducing SQLITE_DEFAULT_CACHE_SIZE
> from 2000 to say 500?. My application mostly writes or updates the
> database and rarely reads from it. Reducing cache pages would help me?
A too small cache will cause more I/O.
I would use a large cache size during the initial load, to avoid
I/O during the building of the btrees, typically some 2
pages of 8192 bytes (164 MB) for large databases (800 MByte or
so).
During inserts and updates the cache has to be big enough to
hold all data- and index pages that will be touched in the
transaction, plus the schema, of course. How much that is, is
hard to estimate; the output of sqlite3_analyzer can give some
insight in this respect.
As always: benchmarks will tell the truth ;)
In my case the on-the-fly virusscan of I/O buffers on our
corporate PC's is the most important cause of slowdown :)
> Thanks,
> Guru
You're welcome.
Regards,
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Index size in file
On Wed, 3 Oct 2007 15:39:06 +0200, you wrote: > > > >I created an index on a TEXT column as I want to be able to >I noticed a large increase in the file size. >Looking at the binary of the file, I see that the index has a copy of all the >data being indexed. >1. Is this necassary? >2. Is there a way to keep the index only in memory and not in the file. > >Clive A long time ago I saw index systems (don't remember, perhaps a mainframe with indexed sequential files), where the B-Tree used simple 'key compression'. Some encoding scheme which replaces the key field by a structure (repeat_length, keypart). Best shown in an example (my keys are 'frank', 'franklin', 'fred', 'google', 'gopher'): - store the full key 'frank' of the first entry in the page as (0,frank) - store 'franklin' as (5,lin), meaning: take the first five characters of the previous key and concatenate the rest. - store 'fred' as (2,ed) - store 'google' as (0,google) - store 'gopher' as (2,pher) This works nicely for large indexes with long keys and a lot of repetition. Of course the effort to handle insertions and deletions is significant. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problems with SQLite and PHP
Hi Markus, On Thu, 04 Oct 2007 23:37:33 +0200, you wrote: >Hello everone, > >I'm having a really weird problem with SQLite when used with PHP - I'm >pretty sure it's not SQLite that's at fault here, but since in the >PHP-DB mailingliste nobody seemed to be able to help me, I'm hoping to >get some insights here :-) [snip] >For the previous discussion in the PHP-DB mailinglist, see here >(starting point, same content as above): >http://marc.info/?l=php-db&m=119134768316086&w=2 > >A detailed step-by-step list for reproducing the problem, including the >used SQL schema, can be found here: >http://marc.info/?l=php-db&m=119143000125909&w=2 > >I hope someone here can point me in the right direction, I'm getting >somewhat desperate :-) > >Thanks, >Markus I can't reproduce the exception (the PDO->query version) on an environment I happened to have available. - MS Windows XP Professional (5.2 build 2600) - Apache 2.2.4 - PHP 5.2.2 - PDO SQLite 1.0.1 2007/03/23 - SQLite library 3.3.16 Perhaps I'll try again tomorrow with the same installation, but using - SQLite library 3.4.2 The only thing i can think of (wild guess) is your php-cli uses another php.ini than the Apache module does. Good luck ! -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] step back
On Fri, 05 Oct 2007 19:06:42 +, you wrote: >[EMAIL PROTECTED] wrote: >> sqlite3_step() is great for scrolling forward through a result set. >> Is there a way to scroll backwards? >> If not, did anyone try implementing it? >> (I guess that the indexes would need backward pointers in order to do it.) >> > >This issue keeps coming up so I did a wiki page. >http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor A true gem! -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] many-one relation
On Mon, 8 Oct 2007 09:04:53 -0700 (PDT), you wrote: >>> Is this a garbage collection situation, where >>> you want a row in B to go away when all >>> referring rows in A are deleted? > >Yes exactly this is what I wanted :) > >Sorry for the confusing message earlier > >-x- >Chetana This might be of help: http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] auto library function loading
On Tue, 9 Oct 2007 07:06:48 -0700 (PDT), you wrote: > Is there a way for SQLITE to automatically load user > defined functions at database instantiation or upon > database connection? You can put a file .sqliterc in the users homedirectory with commands for the command line tool. They will be executed immediately after the database is opened. That might do (part of) what you need. D:\opt\research>sqlite3 tmp/test_05.db3 -- Loading resources from C:\Users\knuytadm/.sqliterc .bail ON .mode tabs select * from sqlite_master; table testTbl testTbl 2 CREATE TABLE testTbl( t_id INTEGER PRIMARY KEY, t_name TEXT ) SQLite version 3.4.2 Enter ".help" for instructions sqlite> Hope this helps. Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Making BEGIN IMMEDIATE the default.
On Wed, 10 Oct 2007 22:10:38 -0500, you wrote:
>On Thu, 11 Oct 2007 02:40:22 +, drh wrote
>> The BEGIN, ROLLBACK, and/or COMMIT only happen on the outermost
>> "transaction". Of course, it is kind of silly to nest
>> as shown above. But this is useful, for example, when each
>> "db transaction" is really in a separate procedure and the
>> procedures are nested.
>
>Wow, I didn't know [transaction] nests! Thanks. I had written some
>untrustworthy code to only invoke [transaction] on the outermost stack frame;
>it's great to know that I can get rid of it.
>
>From the documentation:
>
>"Also, BEGIN does not nest, so you have to make sure no other transactions
Meaning: SQL transactions
> are active before starting a new one. The 'transaction' method takes
> care of all of these details automatically."
And this is about the TCL transaction {} method.
>You might want to be a little bit more clear about the fact that [transaction]
>nests even though BEGIN does not.
The TCL transaction{} can be nested, the SQL BEGIN can't.
As drh wrote:
>> The BEGIN, ROLLBACK, and/or COMMIT only happen
>> on the outermost "transaction".
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Making BEGIN IMMEDIATE the default.
On Fri, 12 Oct 2007 01:00:32 -0500, you wrote:
>On Thu, 11 Oct 2007 13:33:35 +0200, Kees Nuyt wrote
>> On Wed, 10 Oct 2007 22:10:38 -0500, you wrote:
>>> You might want to be a little bit more clear about the fact that
>>> [transaction] nests even though BEGIN does not.
>>
>> The TCL transaction{} can be nested, the SQL BEGIN can't.
>
>It looks like I'm the one who was unclear. I was asking DRH to consider
>expanding the documentation a bit to underscore the fact that [transaction]
>can be used in nested fashion despite the limitations of BEGIN--- I had
>incorrectly assumed that since BEGIN doesn't nest, [transaction] doesn't nest
>either. Also, I didn't explain my typographical convention: [bracketed] words
>are Tcl commands, CAPITALIZED words are SQL keywords.
In retrospect, I could have done a better job reading your
message.
Ok, all clear now ;)
Regards,
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Insert query very slow
Hi Trax, On Sun, 21 Oct 2007 20:44:12 +0200, you wrote: >Hello, >I made a "hello world" program in order to test SQLite, but it is very >slow : >I am trying to set 1000 INSERT in a database. The time average is 2m30s >on a AMD Athlon(TM) XP 2400+ with 1Go Ram >however http://www.sqlite.org/speed.html > >My code >http://pastebin.ca/744623 If you need speed you will have to use transactions. BEGIN; INSERT ; -- thousands of rows COMMIT; Without it, every INSERT is a separate transaction (auto commit) More info: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning >Regards > >trax -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] unsigned integer, text, tutorial, and keys
On Wed, 24 Oct 2007 05:21:03 +0200 (CEST), you wrote:
>Hello,
>
>I have few questions on SQLite3, i'm using it through
>pdo with php5.
>
>- is unsigned int available?
http://www.sqlite.org/datatype3.html
The range of available positive signed integers is large, and
you can avoid negative values with constraints or triggers.
>- can we insert a row with an autoinc primary key, so
>I don't need to provide it when I insert a new row?
Yes.
>- if NULL values are possible for primary keys, then
>autoinc key can't have this NULL on creation but can
>get it on updates if we updates the key with NULL.
>Right ?
If NULL values are possible, the column isn't suited as a
primary key.
>-if NULL values are possibles and primary key is not
>autoincremented then we have to supply the key during
>insert, then there is a risk of having a NOT UNIQUE
>error during insert and also during updates if we
>supply a NULL value during updates. Right ?
Duplicates can be avoided using BEFORE triggers.
You cannot update (a single row) if you can't identify it.
>- As I understood, it's recommended to add NOT NULL
>for primary keys because SQLite allow NULL keys which
>are equals to the first signed int (-xxx48) so it's
>not unique if we have one row already and provide a
>NOT UNIQUE error. Right ?
It is recommended.
>-is there a way to manage foreign keys ?
The syntax is supported and parsed.
You can enforce them with triggers.
http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
>If unsigned int available for SQLite3 :
>- can integer autoincrement primary keys be unsigned ?
>- if we have an unsigned int primary key, with
>autoinc, does it begins with 0 or 1 ?
It is signed and starts at 1.
>About text type:
>- What is the maximum value of a text data ?
http://www.sqlite.org/datatype3.html
>- Do (') have to be escaped (\') in the text string ?
>- is there any ready made function in php to escape
>those (')?
You most likely don't have to escape quotes if you prepare /
bind the values.
>Web links:
>-Do you know good web tutorials, code samples,
>opensource projects showing how to use SQLite mainly
>with php and pdo ?
I am sure there are, but I don't know of any, up to now the
information in the PHP PDO documentation is enough for me.
Google is your friend.
>I read about it on php.net, litewebsite.com and
>sqlite.org indeed.
>
>
>Thanks
>David
HTH
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How to exit from sqlite shell?
On Mon, 4 May 2009 14:00:45 -0400, "D. Richard Hipp" wrote: > >On May 4, 2009, at 1:44 PM, Sam Carleton wrote: > >> prefix with a period: >> >> .exit > >Yes. Also ".quit" or ".q" or Control-D (on most Unix systems > - I don't know if Control-D works on windows) Control-D doesn't work in sqlite3.exe on windows, Control-C does, both in the windows shell (CMD.EXE) and in msys (mingw32) bash. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to exit from sqlite shell?
On Mon, 4 May 2009 15:01:26 -0400, Pavel Ivanov wrote: >In windows shell Control-Z should be equivalent of Control-D on Unix >(it sends EOF to stdin). Yes, you're right. Control-Z, Return does it. The Return key is required to terminate the line editor. On Unix the Control-D by itself is enough. >Pavel -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to check the healthy of database and the indexes of the tables
On Tue, 5 May 2009 13:02:13 -0700 (PDT), Joanne Pham wrote: >Hi All, > I had the database and one of the index is >not good condition. Every time I use the >index by select ... group by .. the result >only return few rows and the message print >out that "database disk image is malformed". > Is there any command to check if the index >or database in good condition. PRAGMA integrity_check; http://www.sqlite.org/pragma.html#debug >Thanks, >JP -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: database disk image is malformed
On Tue, 5 May 2009 16:55:42 -0400, Pavel Ivanov wrote: >Is it just me or somebody else is >seeing too that the sql statement > "select blobid, fbid from sig group by peerid" >is invalid and shouldn't be >executed or prepared at all? You are right, it doesn't make sense. @Joannek: When using group by, your select columns can only use aggregate functions and the columns you group by. Perhaps you meant to use ORDER BY here ? >Pavel -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: database disk image is malformed
On Wed, 6 May 2009 00:40:22 -0500, "Jay A. Kreibich" wrote: >On Tue, May 05, 2009 at 11:46:38PM +0200, Kees Nuyt scratched on the wall: >> On Tue, 5 May 2009 16:55:42 -0400, Pavel Ivanov >> wrote: >> >> >Is it just me or somebody else is >> >seeing too that the sql statement >> > "select blobid, fbid from sig group by peerid" >> >is invalid and shouldn't be >> >executed or prepared at all? >> >> You are right, it doesn't make sense. > > It doesn't make a lot of sense, but it is still valid. > >> @Joannek: When using group by, your select columns can only >> use aggregate functions and the columns you group by. > > "should only", not "can only." SQLite will happily execute that > statement. I stand corrected. >The results are unlikely to be useful, however. Indeed. We had a discussion before about generating an error in these cases. I wouldn't mind, but I'm sure it would break a lot of code. > When SQLite is asked to output a column that is not aggregated or > grouped, the returned value for that column is simply the value > of the last found row in the group. I suspect this is the same for > the grouped columns as well, they just happen to always be the same. > > -j -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 source code modification
On Wed, 6 May 2009 20:59:06 +1000, Maria wrote: >Hi, I would like to modify sqlite source code. >Before, start of modification, I wanted to print 'hello world!' message from >'sqlite3_initialize()' routine. > >I've downloaded sqlite-amalgamation-3.6.13.tar.gz ><http://www.sqlite.org/sqlite-amalgamation-3.6.13.tar.gz>on ubuntu system. >then, I followed the install step, such as: > >>./configure >>make >>make install > >After I install it successfully, I opened the 'sqlite3.c' file and found the >'sqlite3_initialize()' routine and add 'fprintf(stdout, "hello world!\n");' >then, I recompiled. >> make > >and run the sqlite >>sqlite3 test.db >>.tables >>.read createStudent.sql > >etc.. >I thought when I start the sqlite3, it would call initialize routine and >print 'hello world'. but it dosen't. >I also put some printing message in 'sqlite3StartTable()' and >'sqlite3EndTable()' then recompiled it and run create or drop table >statement. > >Two days ago, actually, I could print messages by above way. But since >yesterday, it's never printed anything. I even reinstalled my os system. >I am pretty much confused with it. Could anyone give me some advice, please? In your shell, type: which sqlite3 Does the result point to the result of yur compilation of sqlite3 ? >Thanks so much. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Increasing output
On Wed, 6 May 2009 19:13:29 +0200, Daniel Wolny wrote: >Hi, >Is possible to increase output of -column? > >1 nightwalker nightwalker.szelka.net 1 >3 nightwalker stolezka.pl 1 >4 nightwalker czteroipolkilogramowya 1 >259 nightwalker satan.edu.pl 1 >260 nightwalker prison.net.pl 1 > >czteroipolkilogramowya should be displayed as czteroipolkilogramowyarbuz.pl In the sqlite command line tool: .width 10 13 30 3 >I need -column sorting type. I'm not sure what you mean here. Guessing: To sort by the third column of your result set, use: SELECT * FROM mytable ORDER BY name_of_third_column; or SELECT * FROM mytable ORDER BY 3; >Thanks in advance. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in shell loop
On Wed, 6 May 2009 23:01:24 +0200, Daniel Wolny wrote: >Hi, >Is possible to use sqlite in shell for loop? > >eg. >#!/bin/sh > >for i in `sqlite db "SELECT smt FROM db"` >do > echo "$i" DUPA >done > >I want to act sqlite like any other command in for loop, i mean one >record from db as a one iteration, above will display: > >first1 first2 >second1 second2 DUPA > >I want something like this: >first 1 first2 DUPA >second1 second2 DUPA It should be possible, sqlite sends its output to stdout, so it works like any other unix utility. Just give it a go. Experiment. And enjoy. >Thanks in advance -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 tables hidden from program to program
On Wed, 6 May 2009 14:13:46 -0700 (PDT), rajyalakshmi bommaraju wrote: >Hi, > I started using sqlite3 recently on Ubuntu. I came > across an issue with the database that, I was able to > create database and table from commandline > successfully, I also inserted couple of rows, no > problem. When I tried to open database from C program, > It is fine but I cant access the table. It says that > the table doesnt exist ,I get error when I try to query > from the table. I had to recreate the table from the C > Program then I can insert or read from the table. It > looks like the tables are not global and are hidden > from one program to other. > > Please throw some light on this one. What can I do to > make them accessible from every interested program on > the machine. Did you use the /path/databasefilename parameter on the sqlite command line? As in: # sqlite3 my.db See also: http://www.sqlite.org/quickstart.html If you leave out the databasefilename, sqlite uses an in-memory database, which is destroyed as soon as you leave/quit/exit the command line tool program. >Thanks >rb -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in shell loop
On Wed, 6 May 2009 23:24:00 +0200, Daniel Wolny wrote: >2009/5/6 Kees Nuyt : >> On Wed, 6 May 2009 23:01:24 +0200, Daniel Wolny >> wrote: >> It should be possible, sqlite sends its output to stdout, so >> it works like any other unix utility. >> >> Just give it a go. Experiment. >> And enjoy. >> > >It doesn't work to me: > >#!/bin/sh > >HANDLER=`sqlite -noheader /root/adm/var/database/vhosts "SELECT * FROM >vhosts WHERE login='nightwalker';"` > >for i in "$HANDLER" >do >echo "$i" dupa >done > >Result: >1|nightwalker|nightwalker.szelka.net|1 >3|nightwalker|stolezka.pl|1 >4|nightwalker|czteroipolkilogramowyarbuz.pl|1 >259|nightwalker|satan.edu.pl|1 >260|nightwalker|prison.net.pl|1 dupa That's a shell problem. You can either pick another separator in sqlite or change your shells' IFS. Pavel Ivanovs remarks are relevant as well. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie trying to list resultSet with C
On Fri, 8 May 2009 16:26:20 +0100, Nuno Magalhães wrote: >Greetings. > >I've managed to compile the example, after installing the amalgamation >and using -lsqlite3 in gcc, otherwise it'll complain about undefined >references. > >I can't figure out how to read a simple result set. I know i shoud use >sqlite3_exec and/or sqlite3_step and i'm required to have a >sqlite3_stmt* somewhere, but i can't find good examples and lots of >the ones i find use sqlite3_prepare_v2, which i think is deprecated >for SQLite3... > >Can someone please give me some nice RTFM links will good basic >tutorials for the C API? Ones that include the aforementioned task >preferably ;) http://www.sqlite.org/cvstrac/wiki , more specifically: http://www.sqlite.org/cvstrac/wiki?p=SimpleCode Quickstart C code for executing any SQL against an SQLite database. Very basic but fully functional nevertheless. http://www.sqlite.org/cvstrac/wiki?p=SampleCode Example C code for creating / writing to / reading from a database. >TIA, >Nuno Magalhães -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple counts between two tables
On Sun, 10 May 2009 13:51:29 -0700 (PDT), ckeen wrote: > >Hi, I'm trying to count two different column combinations using two tables. >Table one (tag_name) contains the 'id' and the 'name' for a tag. Table two >is stuffed with entries that links those tags with different applications >and their contents. >Now I would like to select how often each tag is used in each application. >Some of my previous tries worked fine (using JOIN ON) – but only one COUNT >was possible. Now I'm trying to get a statement to work which returns me the >tag amounts for both apps. > >tag_name >id | tag > >1 | sql >2 | xml >3 | foo > >tag_link >id | app | app_id | tag_id > >1 | d| 331 | 2 >2 | t | 49 | 1 > >Here is my current statement: >SELECT > tag_name.id, > (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND >tag_link.app = 't') AS cntTwt, > (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND >tag_link.app = 'd') AS cntDel >FROM > tag_name >GROUP BY > tag_name.id sqlite_version():3.6.13 CREATE TABLE tag_name ( id INTEGER PRIMARY KEY, tag TEXT ); CREATE TABLE app_name ( id INTEGER PRIMARY KEY, app TEXT ); CREATE TABLE tag_link ( app_id INTEGER, tag_id INTEGER, PRIMARY KEY (app_id,tag_id) ); INSERT INTO tag_name values (1,'sql'); INSERT INTO tag_name values (2,'xml'); INSERT INTO tag_name values (3,'foo'); INSERT INTO app_name values (30,'a'); INSERT INTO app_name values (39,'b'); INSERT INTO app_name values (49,'t'); INSERT INTO app_name values (331,'d'); INSERT INTO tag_link values (331,1); INSERT INTO tag_link values (331,2); INSERT INTO tag_link values (49,1); INSERT INTO tag_link values (30,1); INSERT INTO tag_link values (39,2); INSERT INTO tag_link values (331,3); INSERT INTO tag_link values (49,3); SELECT app_name.id,app,COUNT(tag_link.tag_id) AS nrtags FROM tag_link INNER JOIN app_name ON (app_name.id = tag_link.app_id) GROUP BY tag_link.app_id; app_name.id|app_name.app|nrtags 30|a|1 39|b|1 49|t|2 331|d|3 >The parser returns no error, only seems to freeze. Tipps, hints – all kind >of advice. >sincerely, ckeen Hope this helps. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple counts between two tables
On Sun, 10 May 2009 15:09:01 -0700 (PDT), S Fiedler
wrote:
>
>Hi Kees,
>
>thanks for your help. Thats a neater way than I structured my JOIN version
>before. But my goal is to have all tag COUNTs for each application in one
>result row + id and name of the tag. Like:
>
>tag-id | tag-name | count_app_t | count_app_d
>-
>1 | sql | 9| 2
>2 | xml| 61 | 0
>3 | foo | 47 | 826
>
>Until now no 'JOIN construction' allowed more than one COUNT. Thats why I
>tried out the SELECT (SELECT …), (SELECT) way – which didn't though errors,
>but produced the freeze of the script.
Aha, I see, you mean a pivot report.
That can't be easily done in plain SQL.
What is weird in your example, is that the same application
('d') uses the same tag more then once. Is that on purpose?
In other words, it's not completely clear to me what you are
trying to accomplish.
>Regards,
>-steffen
>
>
>Kees Nuyt wrote:
>>
>> On Sun, 10 May 2009 13:51:29 -0700 (PDT), ckeen
>> wrote:
>>
>>>
>>>Hi, I'm trying to count two different column combinations using two
>tables.
>>>Table one (tag_name) contains the 'id' and the 'name' for a tag. Table two
>>>is stuffed with entries that links those tags with different applications
>>>and their contents.
>>>Now I would like to select how often each tag is used in each application.
>>>Some of my previous tries worked fine (using JOIN ON) – but only one COUNT
>>>was possible. Now I'm trying to get a statement to work which returns me
>the
>>>tag amounts for both apps.
>>>
>>>tag_name
>>>id | tag
>>>
>>>1 | sql
>>>2 | xml
>>>3 | foo
>>>
>>>tag_link
>>>id | app | app_id | tag_id
>>>
>>>1 | d| 331 | 2
>>>2 | t | 49 | 1
>>>
>>>Here is my current statement:
>>>SELECT
>>> tag_name.id,
>>> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND
>>>tag_link.app = 't') AS cntTwt,
>>> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND
>>>tag_link.app = 'd') AS cntDel
>>>FROM
>>> tag_name
>>>GROUP BY
>>> tag_name.id
>>
>>
>> sqlite_version():3.6.13
>> CREATE TABLE tag_name (
>> id INTEGER PRIMARY KEY,
>> tag TEXT
>> );
>> CREATE TABLE app_name (
>> id INTEGER PRIMARY KEY,
>> app TEXT
>> );
>> CREATE TABLE tag_link (
>> app_id INTEGER,
>> tag_id INTEGER,
>> PRIMARY KEY (app_id,tag_id)
>> );
>> INSERT INTO tag_name values (1,'sql');
>> INSERT INTO tag_name values (2,'xml');
>> INSERT INTO tag_name values (3,'foo');
>>
>> INSERT INTO app_name values (30,'a');
>> INSERT INTO app_name values (39,'b');
>> INSERT INTO app_name values (49,'t');
>> INSERT INTO app_name values (331,'d');
>>
>> INSERT INTO tag_link values (331,1);
>> INSERT INTO tag_link values (331,2);
>> INSERT INTO tag_link values (49,1);
>> INSERT INTO tag_link values (30,1);
>> INSERT INTO tag_link values (39,2);
>> INSERT INTO tag_link values (331,3);
>> INSERT INTO tag_link values (49,3);
>>
>> SELECT app_name.id,app,COUNT(tag_link.tag_id) AS nrtags
>> FROM tag_link
>> INNER JOIN app_name ON (app_name.id = tag_link.app_id)
>> GROUP BY tag_link.app_id;
>>
>> app_name.id|app_name.app|nrtags
>> 30|a|1
>> 39|b|1
>> 49|t|2
>> 331|d|3
>>
>>
>>>The parser returns no error, only seems to freeze. Tipps, hints – all kind
>>>of advice.
>>>sincerely, ckeen
>>
>> Hope this helps.
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite as a FIFO buffer?
K% "BEGIN{exit 253}"
:ABEND
@echo off
if "%JOB_endmsg%"=="" set JOB_endmsg=No reason specified
echo %MYDATE% %MYTIME% %JOB_TSN% Abend # %JOB_rc% `%JOB_endmsg%`
SQL=UPDATE jobs SET status = 'A', rc = '%JOB_rc%', endmsg = '%JOB_endmsg%',
sysout = CASE WHEN '%JOB_sysout%'=='' THEN
NULL ELSE '%JOB_sysout:log/=%' END WHERE TSN=='%JOB_TSN%';
%GNU_ECHO% "%SQL_PFX%%SQL%" | %SQLITE% %PAR_SDB%
if errorlevel 1 goto SQLERR
call :SHJOBSTA
:: The user proc may have failed, but the dispatcher is healthy
exit /B 0
:SQLERR
echo Error during SQL processing, can't continue
echo Offending statement:
echo %SQL%
exit /B 3
:: ==
:: level 1 subroutines called by MAIN
:: ==
:: --
:: Dispatch a job (fetch from queue, execute)
:: --
:DISPATCH
:DISPNEXT
call :GETJOB
if errorlevel 1 goto R
call :GETDT
if "%JOB_TSN%"=="" goto DISPDONE
set JOB_sysout=log/%JOB_TSN%.txt
PROMPT $$$S
call :EXEJOB
if errorlevel 1 goto R
PROMPT $P$G
call :GETDT
echo %MYDATE% %MYTIME% 8 %0 %PAR_SSN% %JOB_TSN% %JOB_rc% %JOB_endmsg%
echo %MYDATE% %MYTIME% 8 %0 %PAR_SSN% %JOB_TSN% %JOB_rc%
%JOB_endmsg%>>log\log.txt
goto DISPNEXT
:DISPDONE
call :GETDT
echo %MYDATE% %MYTIME% 9 %0 %PAR_SSN%
echo %MYDATE% %MYTIME% 9 %0 %PAR_SSN% >>log\log.txt
goto Z
::
:: Reset a TSN from status A to W
:RESET
set SQL=UPDATE jobs SET status = 'W' WHERE TSN=='%1' AND status IN
('A','I','T','R');
%GNU_ECHO% "%SQL_PFX%%SQL%" | %SQLITE% %PAR_SDB%
if errorlevel 1 goto SQLERR
set PAR_RESTART=
goto Z
::
:: level 0 MAIN entrypoint
:: dispatch [RESTART=TSN#] [SSN=SSN#] [SDB=jobdbpathfile]
::
::
:MAIN
call \data\opt\cfg\setenv.cmd
call \data\opt\cfg\setdir.cmd
set SQL_PFX=.echo off\n.bail on\n.timeout 1000\n
cd /D %0\..
call \data\opt\cfg\%cfg%\setdrives.cmd >log\drives.txt
if errorlevel 1 goto P01
:: reset all possible parameters
for %%p in (RESTART SDB SSN) do set PAR_%%p=
:: set defaults
:: - schedule serial number (TSN is fetched from the job database)
set PAR_SSN=
:: - job database
set PAR_SDB=%DRIV6%\data\opt\db\li\job.db3
:GETPAR
if "%1"=="" goto PROCESS
if "%2"=="" goto P02
set PAR_%1=%2
shift
shift
goto GETPAR
:PROCESS
set PAR_ >log\SSN#%PAR_SSN%.txt
set DRIV >>log\SSN#%PAR_SSN%.txt
if DEFINED PAR_RESTART call :RESET %PAR_RESTART% >>log\SSN#%PAR_SSN%.txt
set PRV_TSN=
echo %MYDATE% %MYTIME% 1 %0 %PAR_SSN% %PAR_SDB% >>log\log.txt
echo %MYDATE% %MYTIME% 1 %0 %PAR_SSN% %PAR_SDB% >>log\SSN#%PAR_SSN%.txt
:: dispatcher loop, one task at a time
call :DISPATCH >>log\SSN#%PAR_SSN%.txt 2>&1
if "%PAR_SSN%"=="" goto Z
@cls
@exit /b 0
:: MAIN Environment and Parameter errors
:P01
echo Can't get all required driveletters.
goto R
:P02
echo Parameters must be specified as pairs 'name value' or 'name=value'
echo dispatch [RESTART=TSN#] [SSN=SSN#] [SDB=jobdbpathfile]
goto R
:: Dispatcher errors
:R
PROMPT $P$G
exit /B 1
:Z
@echo off
=== php fragment to create a job with or without parameters ====
=== it's part of a class which extends PDO
=== I prefer php_pdo_sqlite_external
function enter_job($userid,$cmnd,$parlist,$ntuid,$ntpsw,$jobprio = 8){
$msg = '';
/* Execute a prepared statement by passing an array of values
--> */
$sql = 'INSERT INTO jobs (userid,cmnd,pars,jobprio) VALUES
(:userid,:cmnd,:pars,:jobprio)';
$stjob = $this->prepare($sql);
$this->beginTransaction();
$stjob->execute(array(':userid' => $userid, ':cmnd' => $cmnd,
':pars' => '@list', ':jobprio' => $jobprio));
/*
*
* This will accommodate up to 99 999 999 requests,
* then we have to reset by deleting the database.
* It will be rebuilt automatically
* 01234567 oO0
*/
$jobid = $this->lastInsertId();
$tsn = substr(1000 + $jobid,4,4);
if (isset($parlist)){
/* there are parameters */
$sql = 'INSERT INTO pars (jobid,partx) VALUES
(:jobid,:partx)';
$stpar = $this->prepare($sql);
if (is_array($parlist)){
/* we got a text array with params */
foreach ($parlist as $aval){
$stpar->execute(array(':jobid' =>
$jobid, ':partx' => $aval));
}
} else if (is_object($parlist)) {
/* we got a resultset from a query as paramlist
*/
while ($row = $parlist->fetch(PDO::FETCH_NUM)){
$stpar->execute(array(':jobid' =>
$jobid, ':partx' => $row[0]));
}
}
} else {
}
$this->commit();
/*
* ugly code to launch the dispatcher asynchronously using
* Windows schtasks.exe is left to the imagination of the reader
*/
}
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite as a FIFO buffer? (How make atomic?)
On Mon, 18 May 2009 12:17:25 -0700 (PDT), Allen Fowler wrote: > >> >I have several CGI and cron scripts and that I would like coordinate via a > >> "First In >> >/ First Out" style buffer.That is, some processes are adding work >> >units, and some take the oldest and start work on them. >> > >> >Could SQLite be used for this? >> > >> >> For what it's worth, here you go. >> Perhaps you can borrow a few ideas from it. >> > > >Thank you for posting the code. >I'll try to look through it. > (Like I said, I've never used complex SQL before... and for me this is > complex.) >Can you point me to the part that takes care of making >an atomic removal of a task from the queue, such that >one and only one worker process can get access to a task? >That's what's got me stumped. It's not guaranteed here, I think. The code is used on a site with very low concurrency. My 'solution' has only one worker, the dispatcher. I use schtask.exe to schedule dispatchers, it was the only way I could find to run something on windows outside the context of Apache/PHP. (the at utility would have been better, but it wasn't available to my account profile). Every time a new job is submitted, any previously scheduled dispatchers (which don't run yet) are removed from the scheduler queue. Then the new dispatcher is scheduled to run. Once it starts, the dispatcher runs all waiting jobs it can find, one by one, and exits when all jobs are done. In hindsight I don't like my code that much ;) So I guess this doesn't solve your problem. On Linux/Unix, you could implement a similar dispatcher, which would be the only process which removes tasks from the sqlite queue and starts each task as a background job. >The simple solution would just create a race condition... i think: > >1) INSERT INTO status_table FROM SELECT oldest task in queue >2) DELETE task in queue > >Right? It might work fine if you wrap it in an exclusive transaction. >Thank you, >AF > > > >P.S. > >Am I correct to assume your code is a more flashed out version of what I was >trying to do before > >Table: task_log => (id, task_data, time_stamp) >Table: task_fifo = > (id, fk_task_log) >Table: task_status_log => (id, fk_task_log, status_code, time_stamp) > >And in psudo SQL: > >TRIGGER ON INSERT INTO task_log: >BEGIN >INSERT INTO task_fifo (fk_task_log) VALUES (NEW.id) >END; > >TRIGGER ON DELETE FROM task_fifo: >BEGIN >INSERT INTO task_status_log VALUES (OLD.fk_task_log, "CLAIMED") >END; > > >And then, again in psudo SQL, the worker does something like: > >DELETE 1 OLDEST FROM task_fifo; I don't think it is exactly the same. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] most efficient way to get 1st row
On Tue, 19 May 2009 11:26:31 -0400, Sam Carleton wrote: >Marco Bambini wrote: >> SELECT ... LIMIT 1; >> >Marco, Is this to say that adding the LIMIT 1 does make it more efficient? Not necessarily. Imagine a SELECT with an ORDER BY which makes SQLite sort the resultset before it can return the first row in the resultset. Need I say more? http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Outer Join question?
On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag wrote: >Hallo, > >I have a table 'person' and a table 'group'. Every person can join none, >one or more groups. >No I want to select all persons except those who are member in group 1. >- Sounds simple, but not for me. This is an n:m relationship. If group has more attributes (columns) than just its number, you need a third table: person_group. Then join person with person_group where group_id != 1; The person_group table could be called membership, if you like. >Thanks in advance >Leo -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create indexed view
On Tue, 26 May 2009 14:44:25 +0800, wying wy wrote: >Hi > >May I know if we can create index on a View? You can't create an index on a VIEW. A VIEW can be seen as a stored SELECT statement. >Thanks in advance. >wying -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deleting database content : SQLite3 API
On Sat, 30 May 2009 17:50:36 +0530, wrote: > >Yes , I understand that. Infact I was doing that >through a script during system startup. I wanted >to know whether SQLite provides any API to do the same. No, it doesn't. You could write it yourself: foreach $name in \ sql(SELECT name FROM sqlite_master WHERE type='table';) do sql(DELETE FROM $name;) done If you use a startup script, there is no need for the C API, you might as well do something like: sqlite3 dbfile .schema|sqlite3 dbfile.new or (if the database is not overly large) sqlite3 dbfile .dump|grep - v INSERT|sqlite3 dbfile.new You may want to add a few initialization PRAGMA's to the pipe. >Thanks and Regards, >Souvik >-Original Message- >From: sqlite-users-boun...@sqlite.org on behalf of John Stanton >Sent: Sat 5/30/2009 5:30 PM >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Deleting database content : SQLite3 API > >An Sqlite database is just a file. Delete the file when you start >your program and when you open the database Sqlite will create a fresh >dne, a very low overhead process.. >. >souvik.da...@wipro.com wrote: >> Hello, >> >> Please let me know if we have an API in SQLite3 which allows me to >> retain the database but delete it's content at runtime. The problem I am >> facing is that : Every time I restart my system , I need to create the >> database. If the database exits already it's contents need to be >> deleted. The issue is that the contents of the database varies during >> one power ON - Power OFF cycle . As a result , after finding that the >> database already exits at the system startup, I cannot just drop the >> tables. ( As the table which are present in the existing data base is >> not known. ) >> >> I am using sqlite3wrapped C++ library APIs. >> >> Thanks and Regards, >> Souvik -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Mon, 1 Jun 2009 04:38:37 -0700 (PDT), "Manasi Save" wrote: >Hi, > >we are developing an application on android we are using SQLite Database >and on phone we are getting SQLiteException:no such table. but, it is >working fine on simulator. > >Can anyone provide any input on this? ASCII versus UTF-8 or UTF-16? -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Indexing
On Mon, 1 Jun 2009 12:32:26 +0200, "Ralf" wrote: >Hello, >[>> ] considering a m:n relation a.id <- a.id,b.id -> b.id, is it due to >performance, advisable to put an index on a.id,b.id ? a_id,b_id should be unique in the relationship table, so you should make (a_id,b_id) the primary key to enforce that constraint. >Thanks >Ralf -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should we upgrade the SQLite to 6.6.14.2 from 3.59
On Mon, 1 Jun 2009 15:39:11 -0700 (PDT), Joanne Pham wrote: > I send this email to the group to ask the question > just in case if someone in group has done the > benchmark then it will save my time. You are the only one who can run that benchmark, because no one else knows your application, its databaseschema and its data. General benchmark results will tell you almost nothing about the effect on _your_ application. There have been some speed improvements between 3.5.9 and 3.6.14.2 but certainly nothing like a 4-fold improvement. If you are interested in what happened to sqlite, a weekly peek at http://www.sqlite.org/cvstrac/timeline is worth the effort. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew wrote: > >hey thanx for the reply... u leave the things happening inside.. wat i jus >wanna do is i wanna insert a new row to a table >the table will be like this >stock_id PKproduct_id FK quantitystock_date >1 10001028-05-2009 >10001 1001 527-05-2009 > >and wen i insert a new row with values NULL, 1000, 15,30-05-2009 > >i dont want want it as a new recorde i jus want to update the first row coz >its also having the same product id i jus want set the quantity = 10+15 and >the date new date that is 30-05-2009 >and suppose if i insert row with different product_id it should be inserted >as it is.. Pseudocode: BEGIN; UPDATE stock_tab SET . WHERE stock_id = 1; if sqlite_error() INSERT INTO stock_tab SET (...) VALUES (...); endif COMMIT; -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax to set the conflict resolution of a transaction
On Tue, 2 Jun 2009 08:40:01 -0300, Karl Brandt wrote: >I'm trying to set the conflict resolution of an entire transaction by >using the ON CONFLICT clause without success. > >I'm using the following syntax: > >BEGIN ON CONFLICT ROLLBACK; >INSERT INTO TableX (Id) Values (1); >INSERT INTO TableX (Id) Values (2); >INSERT INTO TableX (Id) Values (3); >COMMIT; > >But get the error (using 3.6.14.2): SQLITE_ERROR - near "on": syntax error > >I found that syntax at the mail archives: > >http://thread.gmane.org/gmane.comp.db.sqlite.general/1563 >http://thread.gmane.org/gmane.comp.db.sqlite.general/5200 >http://thread.gmane.org/gmane.comp.db.sqlite.general/2276 >http://thread.gmane.org/gmane.comp.db.sqlite.general/1562 > >I also tried the syntax found in the SQL wikipedia page: > >BEGIN; >[..] >IF ERRORS=0 COMMIT; >IF ERRORS<>0 ROLLBACK; > >Also no luck. > >Is there a way to set the conflict resolution for an entire transaction? It's not part of the syntax of BEGIN. http://www.sqlite.org/lang_transaction.html As far as I can tell you'll have to use it in every INSERT statement, which has implications for your program flow. http://www.sqlite.org/lang_insert.html http://www.sqlite.org/lang_conflict.html (You probably already read those pages, I included the links for the convenience of other readers) >Luiz -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax to set the conflict resolution of a transaction
On Tue, 2 Jun 2009 10:35:12 -0300, Karl Brandt wrote: >2009/6/2 J. King >> >> On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt >> wrote: >> >> > I'm trying to set the conflict resolution of an entire transaction by >> > using the ON CONFLICT clause without success. >> > >> > [...] >> > >> > Is there a way to set the conflict resolution for an entire transaction? >> >> Such a thing is not possible. You may specify a conflict resolution on a >> given statement (eg. 'INSERT OR ROLLBACK') or on a table (on PRIMARY KEY, >> NOT NULL and UNIQUE constraints), but not on a transaction. > >Thanks for the info. > >Let me explain the complete picture so someone can help me. > >I develop a wrapper around sqlite that tracks the changed records and >than save the changes to the database by building and executing a SQL >query (a transaction). > >Currently it executes the SQL and check the return value. >If the return value is different from SQLITE_OK it executes a >separated ROLLBACK command so another transaction can be started. > >The problem is that after the ROLLBACK command, sqlite3_errmsg will >return "no error", giving the user no clues of what happened. > >I tried INSERT OR ROLLBACK syntax but it will work only for >SQLITE_CONSTRAINT. I would need to handle also SQLITE_ERROR. > >So there's a way to check if a transaction failed (for constraint or >another error) and than rollback without clearing the error message >returned by sqlite3_errmsg? After a ROLLBACK; there is no error (ROLLBACK is succesful), so the error message will be cleared. You can use INSERT .... ON CONFLICT ABORT ... ; Catch the constraint error, fetch the sqlite3_errmsg() and ROLLBACK yourself. http://www.sqlite.org/lang_conflict.html (untested) >Thanks in advance. > >Luiz -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Before Update trigger question
On Tue, 2 Jun 2009 09:38:18 -0700 (PDT), Boris Ioffe
wrote:
>
>Hello Gang,
>This is my first question on this mail list. I noticed that BEFORE UPDATE
>trigger goes off even for insert statements.
>
>My example:
>CREATE TRIGGER validate_players_update BEFORE UPDATE ON players
>WHEN (new.role in (1,2) and
>(select count(*) from players where table_group_id =
> new.table_group_id
>and table_id = new.table_id
>and role = new.role))
>
>BEGIN
>SELECT RAISE(FAIL, "1002: Can not sit player at this role at the
> table");
>END;
>
>
>INSERT INTO players (device_id,table_group_id,table_id,role ) VALUES((select
>device_id from registrations where mesg_token ="aa"), 1, 2 , 2);
>2009-06-02 10:43:36,086 SQLEngine->pysqlite2.dbapi2.IntegrityError
>Traceback (most recent call last):
> File "SQLEngine.py", line 39, in executeUpdate
>self.cur.execute(SQL, args)
>IntegrityError: 1002: Can not sit player at this role at the table
>
>
>Can someone please shed a light on this issue?
>Thanks,
>Boris
I couldn't reproduce your problem. See code below.
(By the way, you really shouldn't use double quotes for
string literals!)
The validate_players_update never fires.
The validate_players_insert does.
If I comment the validate_players_insert trigger out, no
trigger fires.
Please provide a script that demonstrates the problem.
It should run against the command line tool, like the SQL
below, so things aren't obfuscated by a wrapper.
sqlite_version():3.6.13 -- yeah, I should update.
CREATE TABLE players (
table_group_id INTEGER,
table_idINTEGER,
device_id INTEGER,
roleINTEGER
);
CREATE TABLE registrations (
mesg_token TEXT,
device_id INTEGER
);
CREATE TRIGGER validate_players_insert
BEFORE INSERT ON players
WHEN (new.role IN (1,2) AND
(SELECT count(*) FROM players
WHERE table_group_id = new.table_group_id
AND table_id = new.table_id
AND role = new.role))
BEGIN
SELECT RAISE(FAIL, '1001: Insert');
END;
CREATE TRIGGER validate_players_update
BEFORE UPDATE ON players
WHEN (new.role IN (1,2) AND
(SELECT count(*) FROM players
WHERE table_group_id = new.table_group_id
AND table_id = new.table_id
AND role = new.role))
BEGIN
SELECT RAISE(FAIL, '1002: Update');
END;
INSERT INTO registrations VALUES ('aa',1);
INSERT INTO registrations VALUES ('bb',2);
INSERT INTO players (device_id,table_group_id,table_id,role
) VALUES (
(SELECT device_id FROM registrations
WHERE mesg_token = 'bb')
, 1, 2, 2);
INSERT INTO players (device_id,table_group_id,table_id,role
) VALUES (
(SELECT device_id FROM registrations
WHERE mesg_token = 'aa')
, 1, 2, 2);
SQL error near line 38: 1001: Insert
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++
On Tue, 2 Jun 2009 21:30:51 +0200, Sylvain Pointeau wrote: >... because I experienced C++ to be easier with the classes and resource >management via the destructor.I was just wondering why C++ is not used? > >was it for a performance issue? >or a compiler issue? What Virgilio said: : Because there are many platforms that : sqlite runs (and can run at some time) : that doesn't have a C++ compiler : available, but they always have a : C compiler. >or anything else? C is more portable than C++ (fewer dialects, more standardized). For ease of use on platforms with a decent C++ compiler, there are good C++ wrappers. So, there is a choice for application development. >I just read the Linus Torvalds comment on the C++ for Git >What do you think? > >Cheers, >Sylvain -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Before Update trigger question
On Tue, 2 Jun 2009 12:28:31 -0700 (PDT), Boris Ioffe
wrote:
>
> Kees,
> Thank you very much for quick prototype. I will use single
> quotes from now on. It turns out Igor was right.
> I had another trigger.
Yes, always read Igor's replies first ;)
Triggers are very powerful, yet tricky. Nevertheless, I
think it's a good idea to use TRIGGERs (and CONSTRAINTs) as
much as possible instead of application code.
>CREATE TRIGGER insert_players_timeStamp AFTER INSERT ON players
>BEGIN
> UPDATE players SET create_ts = DATETIME('NOW', 'localtime')
> WHERE rowid = new.rowid;
>END;
>
>
> it ticked another update trigger. I found workaround by
> adding UPDATE OF clause for a specific field
>
>CREATE TRIGGER validate_players_update_role BEFORE UPDATE OF role ON players
>
>
>Now I even understand why it works.
>Thanks a lot,
You're welcome, have fun.
>-B
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
On Wed, 3 Jun 2009 00:42:53 -0700 (PDT), Harold Wood wrote: >you should use the insert or replace statement, >it inserts if the row doesnt exist, if the row >does exists then it updates the row. No, that doesn't fulfil the requirement, because quantity isn't incremented. >--- On Wed, 6/3/09, robinsmathew wrote: > > >From: robinsmathew >Subject: Re: [sqlite] how can we solve IF EXIST in SQLite >To: sqlite-users@sqlite.org >Date: Wednesday, June 3, 2009, 3:15 AM > > > >its showing an error near "if": syntax error > > >Kees Nuyt wrote: >> >> On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew >> wrote: >> >>> >>>hey thanx for the reply... u leave the things happening inside.. wat i jus >>>wanna do is i wanna insert a new row to a table >>>the table will be like this >>>stock_id PK product_id FK quantity stock_date >>>1 1000 10 28-05-2009 >>>10001 1001 5 27-05-2009 >>> >>>and wen i insert a new row with values NULL, 1000, 15, 30-05-2009 >>> >>>i dont want want it as a new recorde i jus want to update the first row >coz >>>its also having the same product id i jus want set the quantity = 10+15 >and >>>the date new date that is 30-05-2009 >>>and suppose if i insert row with different product_id it should be >inserted >>>as it is.. >> >> Pseudocode: >> BEGIN; >> UPDATE stock_tab SET . WHERE stock_id = 1; >> if sqlite_error() >> INSERT INTO stock_tab SET (...) VALUES (...); >> endif >> COMMIT; >> -- >> ( Kees Nuyt -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT-ish] ResultSet size
On Sun, 7 Jun 2009 12:47:58 -0500, P Kishor wrote: >2009/6/7 Nuno Magalhães : >> Greetings, >> >> I'm using SQLite for an academic project, through Java's JDBC >> (sqlitejdbc-0.5.4.jar). After executing a simple select, i can iterate >> the ResultSet all the way (showing to output), no problem. The >> problem, which may be silly, is that i need to get a row count so i >> can initialize a variable. >> >> I tried using rs.getFetchSize() but it returns 0. This is the only >> method i could relate to "getting number of rows" from the method >> list. >> >> I tried rs.last(); but get "SQLException: ResultSet is >> TYPE_FORWARD_ONLY" and wouldn't be able to get back anyway. >> >> I tried iterating the set and using rs.isLast() but i get >> "SQLException: function not yet implemented for SQLite". >> >> I know this si more related to JDBC than SQLite, but maybe someone can >> give me a hint? > >Either run a SELECT Count(*) prior to running your full select query, >or run the SELECT query, iterate over it counting the records, find >the total number in the set, and then iterate over it and display as >you are doing now. Either way, you would have to do a two-pass. I >would prefer the two SELECTs, once for the Count() and second time for >the query, for small results sets ... keep in mind, SELECT Count() in >SQLite is not optimized. It has recently been optimized, but only for the form: SELECT count(*) FROM without WHERE or LIMIT clauses. http://www.sqlite.org/cvstrac/chngview?cn=6316 >For big result sets, I might want to just do >one mongo select and then count the results in memory. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has mulitple threads using the same connection
On Tue, 9 Jun 2009 12:06:44 -0700 (PDT), Joanne Pham wrote: > > >Hi All, >What was the problem with the SQLite library is builded >with DSQLITE_THREADSAFE=1 but the application is using >multiple threads with the same connection. >Thanks, >JP Joannek, I think this same issue was discussed very recently in the mailing list. You may want to consult the archives. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has mulitple threads using the same connection
On Tue, 9 Jun 2009 15:23:42 -0700 (PDT), Joanne Pham wrote: >Sorry Couldn't locate the email about Compite with> DSQLITE_THREADSAFE=1 bu the application has multiple >threads using the same connection? >Would you pleas direct me to any document that has this info. >Thanks, >JP As far as I can tell there is no problem with it, if there ever was a problem with it is has been solved. Perhaps you were thinking of this article: To: Subject: Re: [sqlite] SQLite spawns multiple processes? From: "D. Richard Hipp" Date: Fri, 15 May 2009 14:03:05 -0400 Of course you have to search the ticket database. http://www.sqlite.org/cvstrac/search?t=1&c=1 Any problem ever reported is registered there. >____ >From: Kees Nuyt >To: sqlite-users@sqlite.org >Sent: Tuesday, June 9, 2009 12:52:47 PM >Subject: Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has >mulitple threads using the same connection > >On Tue, 9 Jun 2009 12:06:44 -0700 (PDT), Joanne Pham > wrote: > >> >> >>Hi All, >>What was the problem with the SQLite library is builded >>with DSQLITE_THREADSAFE=1 but the application is using >>multiple threads with the same connection. >>Thanks, >>JP > >Joannek, > >I think this same issue was discussed very recently in the >mailing list. You may want to consult the archives. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE with inline view/ derived table
On Thu, 11 Jun 2009 20:17:59 +0200, Frank Naude wrote: >Hi, > >I need some help getting this UPDATE to work with sqlite 3.3.8: > >UPDATE fud28_read >SET user_id=2, msg_id=t.last_post_id, last_view=1244710953 >FROM (SELECT id, last_post_id FROM fud28_thread WHERE forum_id=4 AND >last_post_date > 0) t >WHERE user_id=2 AND thread_id=t.id > >Error: near "FROM" - syntax error: HY000 > >Does sqlite support inline views/ derived tables within UPDATE >statements? Any suggestions on how to get it to work? Not directly, but you can update "the tables behind" a view with an INSTEAD OF trigger. The trigger can contain any update statement you like. http://www.sqlite.org/lang_createtrigger.html#instead_of_trigger >Best regards. > >Frank -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql query with sqlite3_exec
On Fri, 12 Jun 2009 07:05:36 -0700 (PDT), sql_newbie wrote: > >I have another question about sqlite3_exec : > >How can i interact with the database and save the result in a C string for >forther use. For example: > >sqlite3_exec( db, "SELECT FROM urls", NULL, NULL, &zErrMsg ); > >How can i save the returned result-table in a C string for further use in >the program? Have a look at some sample code: http://www.sqlite.org/cvstrac/wiki?p=SimpleCode http://www.sqlite.org/cvstrac/wiki?p=SampleCode >Thanks. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database logic in TRIGGER and CONFLICT, or in software ?
On Sat, 13 Jun 2009 23:42:21 +0100, Simon Slavin wrote: >I'm writing an application which involves lots of relations between >tables. Seen from a high level, my application will have to enforce >lots of rules to ensure database integrity. Before I used SQLite I >would have enforced all these rules in my software. But SQLite has >lots of ways to do this itself, with ON CONFLICT, TRIGGERs, and stuff >like that. But I don't see any real reason to use these features, and >I'm concerned about how well I can document what each of them is there >for. > >I'm an experienced programmer and will have no problem enforcing the >rules in my software. On the other hand, SQLite does some of them >very neatly, with less code than I'd need in my application. On the >gripping hand, if my software fails to do an operation it knows why >and can generate a specific error message, whereas if SQLite hits a >CONFLICT my error message would have to list all the possible reasons >and let the user decide which one was the cause. That's a trade off you have to decide on for yourself. User input should be validated by the application anyway, so the most common errors will be handled by the application. Using CONSTRAINTs and TRIGGERs protects you against programming errors, I would . >Do any of you have experience with doing this ? Are there some >conclusive points which will make me decide immediately that I should >do it one way or the other ? I accept reasoned argument, URLs, >anecdotes, or anything else relevant. I tried to enforce consistency and integrity by implementing a "value domain" system in awk. The schema source uses domain names instead of types. They are simply substituted by the domain definition. The utility primes a new database, creates dictionary tables and registers domains, tables, columns, including the comments from the schema definition, together with dtcreated and dtmodified timestamps. It also keeps a log of all DDL and DML passed through it, loads .csv files by generating INSERT statements, trims values, and optionally analyses the datatypes, min and max values, and min and max length of the values. It focuses on creating (portentially large) databases in batch. No support for referential integrity. It's undocumented, and I don't have time to answer any questions about it, so it's not fit for publication. Snipped of such a schema: --[domains] longname = VARCHAR(64) -- long name alphanum_64 shortname = CHAR(8) -- identifier (userid, account, ...) longtext = CLOB-- text field of arbitrary length counter = INTEGER -- integer --[help] CREATE TABLE %OBJECT% ( -- hlpforshortname, -- knowledge domain hlpname longname, -- name or short description hlptext longtext, -- descriptive text PRIMARY KEY (hlpfor,hlpname) ON CONFLICT ABORT ); >By the way, the SQLite documentation is excellent but it's a little >short on examples (unless there are a treasure trove of them somewhere >I missed ?). How would I, for example, make SQLite refuse to delete >an account if any transactions are recorded for it ? Make up your own >schema for the two tables, as long at they're convincing. Referential integrity can be obtained with REFERENCES constraints (foreign key relations). SQLite parses the syntax but doesn't enforce them yet. But the sqlite3 command line tool has a command, .genfkey, which converts those constraints into TRIGGERs that implement them. http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers There's also a site that implements it: http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator >Simon. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database inserts gradually slowing down
On Tue, 16 Jun 2009 14:23:47 +, Jens Páll Hafsteinsson wrote: > Yes, I'm perfectly aware of this and hence I > would expect the disk to be sweating like hell > running this test while the CPU is relatively > relaxed (given that sqlite is disk bound in > this case and not CPU bound). > > But this is not happening; neither the disk nor > the CPU are practically doing anything, which > is a bit strange. It's as if both the disk and > the CPU are waiting for each other or that > sqlite is playing 'nice' behind my back and > giving up the CPU when it shouldn't. Apart from seeks, the disk has to spin until the correct start sector is under the head. Then it can write a database page, perhaps a few database pages. There are a few parameters you can use to optimize this: - PRAGMA page_size - PRAGMA [default_]cache_size - the number of INSERTs per transaction - The schema: INDEX PRIMARY KEY on the first column instead of a non-unique index (if the application allows it) - load the database in order of index(es) Especially a non-unique index with low cardinality has a lot of overhead. >JP -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search in archive
On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond" wrote: > Is it possible to have a search feature for the > archive? Which archive? I'll assume you have 18 different databases and you want to search them in parallel. > I.e. rather than having to do a linear > search through 18 archives for an answer > to a question, have a google-like search > across all of the archives? Yes, make your application multithreaded, one thread for the user interface and 18 for databases. Every dbthread would open a different database. It will only really help if your system has multiple processor cores, and if the databases are each on a different disk. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 data mode for emacs?
On Sun, 21 Jun 2009 10:01:22 -0700, Kelly Jones wrote: >Emacs' "forms mode" lets you edit a text file as though each line were >a database record. > >Is there a similar mode that lets you edit data inside an sqlite3 db? Not that I know of in Emacs. There are several database browsers for SQLite databases though, with an editable grid. I use Sqlite3explorer, SqliteSpy and SqliteManager. The last one is a Firefox add-on. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 data mode for emacs?
On Sun, 21 Jun 2009 12:44:51 -0700, Kelly Jones wrote: >On 6/21/09, Kees Nuyt wrote: >> On Sun, 21 Jun 2009 10:01:22 -0700, Kelly Jones >> wrote: >> >>>Emacs' "forms mode" lets you edit a text file as though each line were >>>a database record. >>> >>>Is there a similar mode that lets you edit data inside an sqlite3 db? >> >> Not that I know of in Emacs. There are several database >> browsers for SQLite databases though, with an editable grid. >> >> I use Sqlite3explorer, SqliteSpy and SqliteManager. The last >> one is a Firefox add-on. > >Do any of these work in VT100 mode? I'm big on command-line stuff. No -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1
On Fri, 26 Jun 2009 17:07:16 -0400, "Greg Morehead" wrote: > >If I close then reopen the database all my memory is recovered. > >Is this by design??? Yes, what you see is probably the page cache. >I was intending on keeping a connection open most of time. That's a good idea, for at least two reasons: - opening a connection has to parse the schema, and though it's fast code, it should be avoided. - the contents of the page cache aren't wasted, it may be re-used by subsequent statements. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
