Re: [sqlite] SQLITE_DEFAULT_FOREIGN_KEYS support please
Yes. I want too that default. But I still need it in de ODBC driver because I don't have a way to enable foreign keys with MS Access via ODBC. > Currently the code base supports SQLITE_DEFAULT_RECURSIVE_TRIGGERS > which controls the default setting for PRAGMA recursive_triggers. > > Can someone please add similar support for a > SQLITE_DEFAULT_FOREIGN_KEYS compiler define that allows the default > setting of PRAGMA foreign_keys to be controlled at compile time? > > Just a couple of lines need to be added around line 343 of sqliteInt.h > and just a couple more around line 1621 of main.c (the flag needed > there is SQLITE_ForeignKeys). > > It's very trivial to make these changes (and I can easily do it > locally), but it would sure be nice if support for enabling foreign > keys by default could make it into the official distribution. > > Then you could just add -DSQLITE_DEFAULT_FOREIGN_KEYS=1 to the > compiler options to have foreign keys always enabled by default. > > Anyone else interested in having support for this compiler option? > > Kyle > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite query performs 10 times slower than MS Access query
Why you change the query?
Use de Access version or someone without joining a subquery.
Is preferible to join table to table (to more tables) than join table to
subqueries.
Emilio
Doyel5 escribió:
> I have a 800MB MS Access database that I migrated to SQLite. The structure of
> the database is as follows (the SQLite database, after migration, is around
> 330MB):
>
> The table ‘Occurrence’ has 1,600,000 records. The table looks like:
> CREATE TABLE Occurrence
> (
> SimulationID INTEGER,SimRunID INTEGER,OccurrenceID INTEGER,
> OccurrenceTypeIDINTEGER,PeriodINTEGER,HasSucceededBOOL,
> PRIMARY KEY (SimulationID, SimRunID, OccurrenceID)
> )
>
> It has the following indexes:
> CREATE INDEX "Occurrence_HasSucceeded_idx" ON "Occurrence" ("HasSucceeded"
> ASC)
>
> CREATE INDEX "Occurrence_OccurrenceID_idx" ON "Occurrence" ("OccurrenceID"
> ASC)
>
> CREATE INDEX "Occurrence_SimRunID_idx" ON "Occurrence" ("SimRunID" ASC)
>
> CREATE INDEX "Occurrence_SimulationID_idx" ON "Occurrence" ("SimulationID"
> ASC)
>
>
>
> The table ‘OccurrenceParticipant’ has 3,400,000 records. The table looks
> like:
> CREATE TABLE OccurrenceParticipant
> (
> SimulationIDINTEGER, SimRunIDINTEGER,OccurrenceID
> INTEGER,
> RoleTypeID INTEGER, ParticipantIDINTEGER
> )
>
> It has the following indexes:
> CREATE INDEX "OccurrenceParticipant_OccurrenceID_idx" ON
> "OccurrenceParticipant" ("OccurrenceID" ASC)
>
> CREATE INDEX "OccurrenceParticipant_ParticipantID_idx" ON
> "OccurrenceParticipant" ("ParticipantID" ASC)
>
> CREATE INDEX "OccurrenceParticipant_RoleType_idx" ON "OccurrenceParticipant"
> ("RoleTypeID" ASC)
>
> CREATE INDEX "OccurrenceParticipant_SimRunID_idx" ON "OccurrenceParticipant"
> ("SimRunID" ASC)
>
> CREATE INDEX "OccurrenceParticipant_SimulationID_idx" ON
> "OccurrenceParticipant" ("SimulationID" ASC)
> The table ‘InitialParticipant’ has 130 records. The structure of the table
> is
>
>
> CREATE TABLE InitialParticipant
> (
> ParticipantIDINTEGER PRIMARY KEY, ParticipantTypeIDINTEGER,
> ParticipantGroupID INTEGER
> )
>
> The table has the following indexes:
> CREATE INDEX "initialpart_participantTypeID_idx" ON "InitialParticipant"
> ("ParticipantGroupID" ASC)
>
> CREATE INDEX "initialpart_ParticipantID_idx" ON "InitialParticipant"
> ("ParticipantID" ASC)
>
>
>
> The table ‘ParticipantGroup’ has 22 records. It looks like
> CREATE TABLE ParticipantGroup
> (
> ParticipantGroupIDINTEGER,ParticipantGroupTypeID INTEGER,
> Descriptionvarchar (50), PRIMARY KEY( ParticipantGroupID )
> )
>
> The table has the following index:
> CREATE INDEX "ParticipantGroup_ParticipantGroupID_idx" ON "ParticipantGroup"
> ("ParticipantGroupID" ASC)
>
>
>
> The table ‘tmpSimArgs’ has 18 records. It has the following structure:
> CREATE TABLE tmpSimArgs (SimulationID varchar, SimRunID int(10))
>
> And the following indexes:
> CREATE INDEX tmpSimArgs_SimRunID_idx ON tmpSimArgs(SimRunID ASC)
>
> CREATE INDEX tmpSimArgs_SimulationID_idx ON tmpSimArgs(SimulationID ASC)
>
>
>
> The table ‘tmpPartArgs’ has 80 records. It has the below structure:
> CREATE TABLE tmpPartArgs(participantID INT)
>
> And the below index:
> CREATE INDEX tmpPartArgs_participantID_idx ON tmpPartArgs(participantID ASC)
>
> I have a query that involves multiple INNER JOINs and the problem I am
> facing is the Access version of the query takes about a second whereas the
> SQLite version of the same query takes 10 seconds (about 10 times slow!) It
> is impossible for me to migrate back to Access and SQLite is my only option.
>
> I am new to writing database queries so these queries might look stupid, so
> please advise on anything you see faulty or kid-dish.
>
> The query in Access is (the entire query takes 1 second to execute):
> SELECT ParticipantGroup.Description, Occurrence.SimulationID,
> Occurrence.SimRunID, Occurrence.Period,
> Count(OccurrenceParticipant.ParticipantID) AS CountOfParticipantID FROM
> (
> ParticipantGroup INNER JOIN InitialParticipant ON
> ParticipantGroup.ParticipantGroupID = InitialParticipant.ParticipantGroupID
> ) INNER JOIN
> (
> tmpPartArgs INNER JOIN
>(
>(
>tmpSimArgs INNER JOIN Occurrence ON (tmpSimArgs.SimRunID =
> Occurrence.SimRunID) AND (tmpSimArgs.SimulationID = Occurrence.SimulationID)
>) INNER JOIN OccurrenceParticipant ON (Occurrence.OccurrenceID =
> OccurrenceParticipant.OccurrenceID) AND (Occurrence.SimRunID =
> OccurrenceParticipant.SimRunID) AND (Occurrence.SimulationID =
> OccurrenceParticipant.SimulationID)
> ) ON tmpPartArgs.participantID = OccurrenceParticipant.ParticipantID
> ) ON InitialParticipant.ParticipantID = OccurrenceParticipant.ParticipantID
> WHERE (((OccurrenceParticipant.RoleTypeID)=52 Or
> (OccurrenceParticipant.RoleTypeID)=49)) AND Occurrence.HasSucceeded = True
> GROUP BY ParticipantGroup.Description, Occurrence.SimulationID,
> Occurrence.Sim
Re: [sqlite] how do I use more than one column as a "combined key"?
I don't undertand what is your question? a) You know to do it in SQL but you don't in SQLite or b) You dont know how to do it in any SQL yaconsult escribió: > SQL newbie here. Sqlite has been a fantastic tool for analyzing, comparing > and correcting lots of account information. But I've hit a roadblock > because I don't know how to treat multiple columns as a kind of combined > key. > > I need to know how to relate two tables > on multiple columns. > > It's been easy when I only had to relate > using a single column. > > The tables are pretty big - 20,000+ entries. > > User Accounts > name > uid > server > login > . > . > . > > Calendar Accounts > server > login > firstname > lastname > . > . > . > > > What I need to be able to do is to check > that the server and login information > in the first table matches one and only > one of the accounts in the second table. > > What I'm having trouble figuring out is > how to use the server and login as > a combined key. > > When I have a single unique value that > I can relate, like a DS ID, I know how > to do that. > > How can I query to find out which user > accounts don't match up with one and > only one calendar accounts? And which > calendar accounts are not associated > with a single user account? > > Thanks for any help you can provide. > Sqlite is the perfect tool for this kind of stuff! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with sqlite in c#.net
do you try a COMMIT? manp escribió: > when i execute a query like this > UPDATE setting SET value="red" WHERE name="color" > the row will update temporary while app is running , when i close app noting > appear in DB > i use ManagedSQLite dll ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default text column values
Are you sure? Send us yours commands that prove that.
I wrote:
CREATE TABLE CurrentValues (
Time_Stamp DATETIME DEFAULT CURRENT_TIMESTAMP,
Sensor_ID WORD DEFAULT 1 NOT NULL,
Sensor_Name VARCHAR(20),
Sensor_Type VARCHAR(8),
Sensor_Value FLOAT DEFAULT 0 NOT NULL,
Sensor_Units VARCHAR(15) DEFAULT 'NotSet' NOT NULL,
Location VARCHAR(20),
Alarm LOGICAL DEFAULT False,
PRIMARY KEY (Sensor_ID));
insert into CurrentValues (Sensor_Name) VALUES ('this name');
select * from CurrentValues;
And I have:
2008-08-02 21:42:05|1|this name||0.0|NotSet||False
SQLite version 3.5.7
You expected somethig else?
Bye
Emilio
> Hi,
>
> I am using SQLite 3.6.0 under Windows. I created a table using the
> following schema (prev used in another DB hence Varchars etc.) :
>
> CREATE TABLE CurrentValues (
> Time_Stamp DATETIME DEFAULT CURRENT_TIMESTAMP,
> Sensor_ID WORD DEFAULT 1 NOT NULL,
> Sensor_Name VARCHAR(20),
> Sensor_Type VARCHAR(8),
> Sensor_Value FLOAT DEFAULT 0 NOT NULL,
> Sensor_Units VARCHAR(15) DEFAULT 'NotSet' NOT NULL,
> Location VARCHAR(20),
> Alarm LOGICAL DEFAULT False,
> PRIMARY KEY (Sensor_ID));
>
> Initially the default values for Time_Stamp and Alarm worked as expected
> but Sensor_Value and Sensor_Units inserted nothing at all.
>
> I changed the Sensor_ID's type from WORD to INTEGER and this column now
> inserts the default value. I changed the Sensor_Units' type to TEXT and
> it made no difference.
>
> Can anyone see why my default text value doesn't work?
>
> David Goadby
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
(sorry about my poor english) I think that the problem doesn't correct by creating a DESC index. The problema was at de 'where clausula': (events.eid<=3261976) For some reason SQLITE doesn't optimize the query to use the index to locate the last index item that have type=22 and eid<=3261976. Of course if you have only a few items that have tid=9, SQL must have to read every item starting with de last until he find the item that haves tid=9. Do you try to the prevoius sugest: add a index that have type and tid? You must know that, SQLite must read the items to find verbose<=1 good luck! Emilio Alexey Pechnikov escribio': > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson написал(а): >> When I do the following query: >> >> SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9) >> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid >> DESC LIMIT 1; >> >> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", >> it's very fast. > > As described in http://www.sqlite.org/lang_createindex.html > "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] > index-name > ON table-name ( column-name [, column-name]* ) > column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" > > You can try create additional index as > CREATE INDEX ev4_idx ON events (type,eid desc); > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] concatenation operator
You must use || (doble pipe) to concatenate strings or what ever. Note that || don't concatenate nulls 'CHE' || null is null sorry about my poor english Joanne Pham escribió: > Hi All, > I had the table, appMapTable as defined below: > appId, appName, appType > 1, appname, 2 > 2, appname, 3 > > appName always has string as appname and now I want to concatenate the > appName with appId > so the result will be: > 1,appname1,2 > 2, appname2,3 > . > I ran the following sql statement : > update appMapTable set appName = appName + appId > but the result is not what I want. Would you help? > Thanks, > JP > > > > > Be a better friend, newshound, and > know-it-all with Yahoo! Mobile. Try it now. > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert date
try inserting year/month/day Emilio > > I have the following table > CREATE TABLE Sighting ( > SightingIdinteger PRIMARY KEY AUTOINCREMENT NOT NULL, > SpeciesId integer, > LocationIdinteger, > SightingDate date, > Note nvarchar(100) > ); > > and the following insert > INSERT INTO Sighting (SpeciesID,LocationID,SightingDate,Note) > VALUES (3005,22,'2/26/2008','New Note') > > the insert works EXCEPT the date keeps coming in as NULL! What am I doing > wrong? > > Thanks > Lonnie > > -- > View this message in context: > http://www.nabble.com/Insert-date-tp16895860p16895860.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] paged query in sqlite
another aproach is to use OFFSET and LIMIT in your select queries: select * from table limit 100 offset 1 then select * from table limit 100 offset 101 then select * from table limit 100 offset 201 etc. Shailesh Madhukar Birari escribió: > Does Sqlite support Paged query? what I mean is on doing select* it should > return me first, say 100, rows and then subsequent calls should return me > successive rows. > Is this supported in sqlite:? If yes, what are the interfaces? > If not, is there an easy way to add this functionality using existing > interfaces? > > regards. > Shailesh > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE in multiple same table
Thank's It works also in PostgreSQL! Emilio Platzer Igor Tandetnik escribió: > "Emilio Platzer" <[EMAIL PROTECTED]> wrote > in message > news:[EMAIL PROTECTED] >> I try (and not works): >> >> CREATE TABLE nodes( >> id integer primary key, >> father_id integer, >> depth integer); >> INSERT INTO nodes (id,father_id,depth) VALUES (9,null,1); >> INSERT INTO nodes (id,father_id,depth) VALUES (99,9,null); >> update nodes set depth= >>(select f.depth+1 >> from nodes as f >> where f.id = father_id) >> where depth is null; >> SELECT * FROM nodes; > > Make it > > update nodes set depth= > (select f.depth+1 >from nodes as f >where f.id = nodes.father_id) > where depth is null; > > This time I actually tested it. It works. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE in multiple same table
I try that solution but don't work. The reason is that in the subquery if a field don't prefix with an alias (or a table name) the parser supouses that was of de first table [[[--- Sorry about my poor Englis ---]]] I try (and not works): CREATE TABLE nodes( id integer primary key, father_id integer, depth integer); INSERT INTO nodes (id,father_id,depth) VALUES (9,null,1); INSERT INTO nodes (id,father_id,depth) VALUES (99,9,null); update nodes set depth= (select f.depth+1 from nodes as f where f.id = father_id) where depth is null; SELECT * FROM nodes; Te output is: 9||1 99|9| Must be: 9||1 99|9|2 I want to have something than do the same that the next sentences but without de creating of a view: CREATE TABLE nodes( id integer primary key, father_id integer, depth integer); INSERT INTO nodes (id,father_id,depth) VALUES (9,null,1); INSERT INTO nodes (id,father_id,depth) VALUES (99,9,null); CREATE VIEW nodes_father AS SELECT * FROM nodes; update nodes set depth= (select nodes_father.depth+1 from nodes_father where nodes_father.id = nodes.father_id) where depth is null; SELECT * FROM nodes; Te output is correct: 9||1 99|9|2 Thanks > "Emilio Platzer" <[EMAIL PROTECTED]> wrote > in message > news:[EMAIL PROTECTED] >> I want to UPDATE a field of one table with data of rows of the same >> table. >> >> For example to calculate the depth of a node in a tree >> >> update nodes as s set s.depth= >>(select f.depth+1 >> from nodes as f >> where f.id=s.father_id) >>where s.depth is null; > > You can't give an alias to the table you are updating, but you can use > the same table (with an alias) in subselect. Just make it > > update nodes set s.depth= > (select f.depth+1 >from nodes as f >where f.id = father_id) > where depth is null; > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE in multiple same table
I want to UPDATE a field of one table with data of rows of the same table. For example to calculate the depth of a node in a tree update nodes as s set s.depth= (select f.depth+1 from nodes as f where f.id=s.father_id) where s.depth is null; I could do this in a ugly way: create view nodes_father as select * from nodes; update nodes set depth= (select nodes_father.depth+1 from nodes_father where nodes_father.id=nodes.father_id) where s.depth is null; But I want to do this without creating a view. The complete example need initialization: update nodes set depth=0 where father_id is null; update nodes set depth=null where father_id is not null; and a repeat execution of the first update until the next statment returns no rows: select * from nodes where depth is null Thanks. I'm sorry about my poor English. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
