Re: [sqlite] Question about speed of CASE WHEN
Or is it just a case of needing to view the final data in a user friendly environment? That is it. They can format, sort, print etc. in Excel. They won't even have to know about SQLite. The SQLite database file can safely be deleted and it will still work the same. RBS Hi RBS, I use SQLite as a data manipulator, not as a database. I get data from a server database, dump to SQLite, manipulate the data and finally dump to Excel. As this is reporting software speed is important, so I will go with the fastest method. OK, I have to ask. What do you then do with the data in Excel? I spend so much of my time with clients converting them from using spreadsheets (80% of the time when it's more appropriate) to using a database, that my ears prick up whenever I hear someone doing the reverse. Can you create whatever facilities you're using in Excel, directly in the SQLite database (eg via CREATE VIEW)? Or is it just a case of needing to view the final data in a user friendly environment? Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] multithread problem
Hi, I probably asked about this before, but as I'm having some problems with my implementation, I would like to ask more clear questions. The main part of the application does not modify the database, but quries it occasionally (select). The other thread is responsible for the changes (import from a file to a table, insert, update). 1. Should I open the database explicitly in the amin part and also in the thread? 2. should I create the tables in the thread or can I create them in the main thread and modify them in the other? My current situation is that I open the database and create the tables in the main thread. When the other thread attempts to import data from a file to a table, no error is reported, but still the table is empty after the import. Thanks, Rafi.
[sqlite] Re: multithread problem
Rafi Cohen [EMAIL PROTECTED] wrote: 1. Should I open the database explicitly in the amin part and also in the thread? In my experience, SQLite works best when every thread opens its own connection. 2. should I create the tables in the thread or can I create them in the main thread and modify them in the other? You can create tables on any thread, it doesn't matter. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: data type problem
qinligeng-9Onoh4P/[EMAIL PROTECTED] 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)' SQLite ignores length restriction. Any cell may store a string of arbitrary length (or, indeed, any other supported data type). For more details, see http://sqlite.org/datatype3.html Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Anyone Having problems with SQLite 3.13 on WinMobile?
Chris Hodges [EMAIL PROTECTED] wrote: Hello: I recently upgraded to the latest version of SQLite 3.13 and ever since then I have been experiencing numerous SQLITE_MISUSE[21] error message. Here are the kinds of things that will cause an SQLITE_MISUSE error: (1) Using the same database connection from two different threads at the same time. (2) Calling any SQLite function other than sqlite3_interrupt() from within a signal handler. (3) Using an sqlite3* or sqlite3_stmt* pointer that is invalid or which has been previously closed or finalized. (4) Calling sqlite3_bind_XXX() on a prepared statement that is currently in the middle of executing. (5) Using SQLite in such a way that it needs to release a file lock from one thread that was obtained on a different thread, on older Linux systems where a OS bug disallowed such operations. Since you are using windows, (5) clearly does not apply to you and as far as I know, neither does (2). But the other three might. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] data type problem
[EMAIL PROTECTED] 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)' This is a feature, not a bug. If you want the database to enforce a maximum string length, then add CHECK( length(DutyID)=32 ) -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: multithread problem
Dne úterý 20 březen 2007 12:42 Igor Tandetnik napsal(a): Rafi Cohen [EMAIL PROTECTED] wrote: 1. Should I open the database explicitly in the amin part and also in the thread? In my experience, SQLite works best when every thread opens its own connection. How should I understand it? It is faster? Much secure or what? I have multithreaded program, where some threads are inserting data into tables (in random moments), two of them are retrieving subsets of data to send it via udp protocol over internet in short data length, and one thread operates as terminal for human users, where (single) user can fed sql statements and retrieve data in human readable format. The architecture looks like this: Single thread locks a common mutex just before and then calls an sqlite api, when it receives excepted data, the mutex is unlocked. Do you think that is there a better method? Thank You Jakub Ladman - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Raise not working.
I have a problem to get RAISE function working. When the trigger is triggered by an insert, i only want to execute the update and skip the first insert. So i tried this with sqlite version 3.3.8 : -- create table blocked ( idcharacter varying(200), ips character varying(40), ipd character varying(40), proto smallint, pdinteger, hits bigint default 1 ); create trigger checkdrop before INSERT on blocked when (select count(*) from blocked where id=new.id) 0 BEGIN when update blocked set hits = hits + 1 where id = new.id; RAISE (IGNORE) END; -- But i have a syntax error. I would like to have an equivalent from postgresql with the return null possibility. Thank you in advance for your help.
[sqlite] Query Issue
Hi, I'm trying to run following query in sqllite, select a.extract_sequence,a.file_location,a.active,b.start_date,b.end_date,b.po rtfolio_code,c.status from extract_master a, extract_parameter b, ( select extract_sequence,status,user_id from extract_status e where datetime = (select max(datetime) from extract_status s where e.extract_sequence = s.extract_sequence) ) c where a.extract_sequence = b.extract_sequence and b.extract_sequence = c.extract_sequence and c.extract_sequence = a.extract_sequence And it gives me an error saying e.extract_sequence does not exist. Then I tried writing the same query using a different syntax, select a.extract_sequence,a.file_location,a.active,b.start_date,b.end_date,b.po rtfolio_code,c.status from extract_master a, extract_parameter b, ( select extract_sequence,status,user from extract_status e where (extract_sequence,datetime) in (select extract_sequence,max(datetime) from extract_status group by extract_sequence ) ) c where a.extract_sequence = b.extract_sequence and b.extract_sequence = c.extract_sequence and c.extract_sequence = a.extract_sequence But, unfortunately this does not work either and gives an error [syntax error near , ] in the above line. Please let me know if there is anyother way to achieve this in SQLite. Regards Ajay THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, copying or use of this message and any attachment is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and permanently delete it from your computer and destroy any printout thereof. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] API enhancement
Ken wrote: It should save some time. How much is questionable. Why would sqlite have to bind the Pointer bound variables? Isn't the strategy of binding to associate a variable with a statment? Why should I have to continually re-associate the bindings with a statement thats allready been prepared and bound, just to execute it again after a reset ? I guess I'm a bit confused, I'll look at the bind code in sqlite some more. Ken, Your idea could save some time but it would require adding a new class of indirect variables (references) to sqlite. The VDBE opcode that loads the variable values would have to be changed to recognize the indirect variables and then create an internal sqlite variable that can be pushed onto the VDBE stack from the external variable. The last part is the same function that the bind routines perform. The bind APIs are fairly lightweight functions, basically just saving the value passed into an internal array. Your scheme would only be saving the overhead of the internal copy operation (from the variable to the stack during the op_variable opcode) and the call to the bind function itself. This scheme would also be adding the cost of the variable type check to every variable lookup. There is also the distinct possibility that a variable may be dereferenced more than once while executing a statement, and this would involve duplicating the work of creating the internal variable from the external memory. There is also the possibility of some nasty SQL bugs due to the value off a variable being changed during the execution of a statement. All in all I don't think the payback is large enough to justify the extra complexity and increased code size in the general case. If you have an application where the overhead of the bind functions calls are a real issue, you could of course create a custom version of sqlite that implements your idea. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] data type problem
[EMAIL PROTECTED] wrote: [EMAIL PROTECTED] 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)' This is a feature, not a bug. If you want the database to enforce a maximum string length, then add CHECK( length(DutyID)=32 ) I think what the OP is saying is that he thinks sqlite3_column_decltype should return the complete declared type char(32) not just the char portion. This seems to be an issue with the way sqlite is parsing the column declaration. I suspect sqlite may be getting confused by the square bracket quoting used on the typename. The syntax grammar at http://www.sqlite.org/lang_createtable.html says that the type of a column can be given as type := typename ( number ) Which the OP is doing, but the type returned by the decltype API only has the typename part. Whether or not the square bracket quoting should be included is debatable. This does seem like a bug to me. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] API enhancement
Denis, Thanks for the great explanation !!! Regards, Ken Dennis Cote [EMAIL PROTECTED] wrote: Ken wrote: It should save some time. How much is questionable. Why would sqlite have to bind the Pointer bound variables? Isn't the strategy of binding to associate a variable with a statment? Why should I have to continually re-associate the bindings with a statement thats allready been prepared and bound, just to execute it again after a reset ? I guess I'm a bit confused, I'll look at the bind code in sqlite some more. Ken, Your idea could save some time but it would require adding a new class of indirect variables (references) to sqlite. The VDBE opcode that loads the variable values would have to be changed to recognize the indirect variables and then create an internal sqlite variable that can be pushed onto the VDBE stack from the external variable. The last part is the same function that the bind routines perform. The bind APIs are fairly lightweight functions, basically just saving the value passed into an internal array. Your scheme would only be saving the overhead of the internal copy operation (from the variable to the stack during the op_variable opcode) and the call to the bind function itself. This scheme would also be adding the cost of the variable type check to every variable lookup. There is also the distinct possibility that a variable may be dereferenced more than once while executing a statement, and this would involve duplicating the work of creating the internal variable from the external memory. There is also the possibility of some nasty SQL bugs due to the value off a variable being changed during the execution of a statement. All in all I don't think the payback is large enough to justify the extra complexity and increased code size in the general case. If you have an application where the overhead of the bind functions calls are a real issue, you could of course create a custom version of sqlite that implements your idea. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Raise not working.
Laurent LAVAUD l.lavaud-F9DrG26JU6dWk0Htik3J/[EMAIL PROTECTED] wrote: I have a problem to get RAISE function working. When the trigger is triggered by an insert, i only want to execute the update and skip the first insert. create trigger checkdrop before INSERT on blocked when (select count(*) from blocked where id=new.id) 0 BEGIN when What is this 'when' doing here? update blocked set hits = hits + 1 where id = new.id; RAISE (IGNORE) RAISE is a function, not a statement. Make it select RAISE(IGNORE); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Raise not working.
Laurent LAVAUD wrote: I have a problem to get RAISE function working. When the trigger is triggered by an insert, i only want to execute the update and skip the first insert. So i tried this with sqlite version 3.3.8 : -- create table blocked ( idcharacter varying(200), ips character varying(40), ipd character varying(40), proto smallint, pdinteger, hits bigint default 1 ); create trigger checkdrop before INSERT on blocked when (select count(*) from blocked where id=new.id) 0 BEGIN when update blocked set hits = hits + 1 where id = new.id; RAISE (IGNORE) END; -- But i have a syntax error. I would like to have an equivalent from postgresql with the return null possibility. Laurent, What you have should work with a couple of minor changes; create table blocked ( idcharacter varying(200), ips character varying(40), ipd character varying(40), proto smallint, pdinteger, hits bigint default 1 ); create trigger checkdrop before INSERT on blocked when (select count(*) from blocked where id=new.id) 0 BEGIN update blocked set hits = hits + 1 where id = new.id; select RAISE (IGNORE); END; HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] API enhancement
Partitioning an API at the wrong level punishes users who have carefully
structured interfaces by needlessly bloating their code. It is hard to
have a Lite embedded application when code bloat swells the library
routines.
Ken wrote:
It should save some time. How much is questionable.
Why would sqlite have to bind the Pointer bound variables? Isn't the strategy of binding to associate a variable with a statment? Why should I have to continually re-associate the bindings with a statement thats allready been prepared and bound, just to execute it again after a reset ?
I guess I'm a bit confused, I'll look at the bind code in sqlite some more.
Scott Hess [EMAIL PROTECTED] wrote: I don't see how your modified version is
any better than just putting
the sqlite3_bind_int() inside the loop. You've superficially lifted
some code out of the loop, but sqlite3_step() is going to have to go
through and bind all of the pointer bound variables in your
suggested API, so it won't save you anything in the end.
-scott
On 3/19/07, ken-33 wrote:
Anyone thoughts?
ken-33 wrote:
Question for the list,
I'd like to optimize my code, using the following pseudo code as an
example.
===
int i = 0 ;
char str[20];
sqlite3_prepare_v2( insert into t1 values (?,?) )
sqlite3_bind_int ( i )
sqlite3_bind_text(str)
BEGIN TRANSACTION
For (i = 0; i 10; i++) {
sqlite3_step ( );
sqlite3_reset( )
}
COMMIT TRANSACTION
==
However, the above code will fail to insert the values for i in the loop.
It will only insert the value 0, since that was the binding value...
An enhancement request would be to allow the user to bind the address to
the statement objects. This would be a huge benefit from the standpoint
of fewer function calls to sqlite3_bind in the inside loop.
So maybe the following API:
sqlite3_pbind_int(sqlite3_stmt *, int, int * );
sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *,
void(*)(void*));
notice the text takes a pointer to the length...
sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *,
void(*)(void*));
Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
SQLITE_STATIC.
Regards,
Ken
--
View this message in context:
http://www.nabble.com/API-enhancement-tf3405347.html#a9562311
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Finding linked peers
I'm having trouble wrapping my head around a solution here. Any advice is appreciated. I'm working on a SQLite-based app for keeping track of PC BIOS releases. One obvious requirement is to be able to track and document change history. So, using this sample schema/data code: create table bios_release ( oid integer primary key, parent_id integer, group_id integer, version text, bios_name text ); insert into bios_release values (1, null, 1, '1.10', 'PC1_v1.1'); insert into bios_release values (2, null, 2, '1.10', 'PC2_v1.1'); insert into bios_release values (3, 1, 1, '1.20', 'PC1_v1.2'); insert into bios_release values (4, null, 3, '1.10', 'PC3_v1.1'); insert into bios_release values (5, 3, 1, '1.30', 'PC1_v1.3'); /* irrelevant to the discussion, but provided for clarity (?) */ create table bios_group ( oid integer primary key, group_name text ); insert into bios_group values (1, 'PC1 Group'); insert into bios_group values (2, 'PC2 Group'); insert into bios_group values (3, 'PC3 Group'); I see two ways to go to know which releases. The obvious way is to simply maintain a history group with an ID number that gets stored in each member BIOS release record. A simple: select * from bios_release where group = 1 order by version desc; will get me all the members of the PC1 Group in descending order. That's fine, but, creating, naming, and maintaining the groups is a hassle for the administrators. The other option I'm considering is chaining each new bios_release to its predecessor by storing the predecessor OID in the parent_id column. Doing it this way makes some sense from the admin process POV because usually, the admin has a new BIOS with a set of known properties, and not necessarily any knowledge of what group it might belong to, or whether or not she needs to create a new group for this BIOS. So, finally, the question: What might the SQL look like to retrieve a list of predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl), and walk the chain in some fashion. Is this result even possible using plain SQL? TIA -Clark - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] involing sqlite3 command as a superuser
Hi, for various reasons, I preffer to invoke my application as the superuser. This, in turn, invokes sqlite3 command to apply a .import command from file to table. Although .import does not return any error, the table remains empty. So, I manually entered to sqlite3 and applied the .import command. Most surprisingly, as a superuser it does just nothing, as ignored. But when I do just the same as a regular user, it works with no problem. So, first, can anybody confirm this or reffer me to other reasons for this? And second, does anybody have any solution for this? As I said, I have to run my application as a superuser as it has to open and communicate with serial ports. Thanks, Rafi.
Re: [sqlite] Finding linked peers
Clark Christensen wrote: So, finally, the question: What might the SQL look like to retrieve a list of predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl), and walk the chain in some fashion. Is this result even possible using plain SQL? Clark, SQLite does not support the recursive SQL queries that could be used to do this kind of processing. So there is no way to follow a chain in SQL. You can convert the problem into pattern matching by having each record store the path along the chain in that record. This is really a variation of the SQL tree problem. I have previously posted a sample implementation of this materialized (or stored) path method at http://www.mail-archive.com/sqlite-users@sqlite.org/msg13225.html HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Finding linked peers
It's much easier to load all the data into a in-memory hierarchical structure and work with it from memory than try to come up with individual SQL statements. Some things you could do easily like find the leaf nodes (i.e., use a sub-select and identify nodes that nobody else uses as a parent) or find the root nodes (parent id is null) but getting a full hierarchy requires multiple SQL statements and thus is faster to process in a custom hierarchical structure rather than through SQL. Also note that using version DESC will not necessarily give you the expected results because it's using string comparison and '10.0' is less than '2.0' with string comparison. You could create a custom collation routine to do proper version number comparison but it'd be faster to store the data as separate integers (a field for major and minor version). HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Clark Christensen [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 20, 2007 1:57 PM To: SQLite List Subject: [sqlite] Finding linked peers I'm having trouble wrapping my head around a solution here. Any advice is appreciated. I'm working on a SQLite-based app for keeping track of PC BIOS releases. One obvious requirement is to be able to track and document change history. So, using this sample schema/data code: create table bios_release ( oid integer primary key, parent_id integer, group_id integer, version text, bios_name text ); insert into bios_release values (1, null, 1, '1.10', 'PC1_v1.1'); insert into bios_release values (2, null, 2, '1.10', 'PC2_v1.1'); insert into bios_release values (3, 1, 1, '1.20', 'PC1_v1.2'); insert into bios_release values (4, null, 3, '1.10', 'PC3_v1.1'); insert into bios_release values (5, 3, 1, '1.30', 'PC1_v1.3'); /* irrelevant to the discussion, but provided for clarity (?) */ create table bios_group ( oid integer primary key, group_name text ); insert into bios_group values (1, 'PC1 Group'); insert into bios_group values (2, 'PC2 Group'); insert into bios_group values (3, 'PC3 Group'); I see two ways to go to know which releases. The obvious way is to simply maintain a history group with an ID number that gets stored in each member BIOS release record. A simple: select * from bios_release where group = 1 order by version desc; will get me all the members of the PC1 Group in descending order. That's fine, but, creating, naming, and maintaining the groups is a hassle for the administrators. The other option I'm considering is chaining each new bios_release to its predecessor by storing the predecessor OID in the parent_id column. Doing it this way makes some sense from the admin process POV because usually, the admin has a new BIOS with a set of known properties, and not necessarily any knowledge of what group it might belong to, or whether or not she needs to create a new group for this BIOS. So, finally, the question: What might the SQL look like to retrieve a list of predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl), and walk the chain in some fashion. Is this result even possible using plain SQL? TIA -Clark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Finding linked peers
Sample recusrive SQL from another database engine... level is a built in field. This is very useful and powerful syntax allowing one to build tree's (ie parent child relationships) inside of a table. SELECT level,chld FROM tbl1 START WITH value = 'some value' CONNECT BY parent = PRIOR(child) Dennis Cote [EMAIL PROTECTED] wrote: Clark Christensen wrote: So, finally, the question: What might the SQL look like to retrieve a list of predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl), and walk the chain in some fashion. Is this result even possible using plain SQL? Clark, SQLite does not support the recursive SQL queries that could be used to do this kind of processing. So there is no way to follow a chain in SQL. You can convert the problem into pattern matching by having each record store the path along the chain in that record. This is really a variation of the SQL tree problem. I have previously posted a sample implementation of this materialized (or stored) path method at http://www.mail-archive.com/sqlite-users@sqlite.org/msg13225.html HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - Dennis Cote [EMAIL PROTECTED] wrote: Clark Christensen wrote: So, finally, the question: What might the SQL look like to retrieve a list of predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl), and walk the chain in some fashion. Is this result even possible using plain SQL? Clark, SQLite does not support the recursive SQL queries that could be used to do this kind of processing. So there is no way to follow a chain in SQL. You can convert the problem into pattern matching by having each record store the path along the chain in that record. This is really a variation of the SQL tree problem. I have previously posted a sample implementation of this materialized (or stored) path method at http://www.mail-archive.com/sqlite-users@sqlite.org/msg13225.html HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Finding linked peers
Dennis, Very cool. Thanks for showing the example. You always seem to offer well-considered solutions. It might just be a practical tool for the job here. I could see the materialized path solution working both for finding the change history, and for producing a browser-based UI for identifying a release's predecessor. -Clark - Original Message From: Dennis Cote [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Tuesday, March 20, 2007 11:27:39 AM Subject: Re: [sqlite] Finding linked peers Clark Christensen wrote: So, finally, the question: What might the SQL look like to retrieve a list of predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl), and walk the chain in some fashion. Is this result even possible using plain SQL? Clark, SQLite does not support the recursive SQL queries that could be used to do this kind of processing. So there is no way to follow a chain in SQL. You can convert the problem into pattern matching by having each record store the path along the chain in that record. This is really a variation of the SQL tree problem. I have previously posted a sample implementation of this materialized (or stored) path method at http://www.mail-archive.com/sqlite-users@sqlite.org/msg13225.html HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Finding linked peers
Also note that using version DESC will not necessarily give you the expected results Gotcha. Always good to remember :-)) Thanks! -Clark - Original Message From: Samuel R. Neff [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Tuesday, March 20, 2007 12:50:12 PM Subject: RE: [sqlite] Finding linked peers It's much easier to load all the data into a in-memory hierarchical structure and work with it from memory than try to come up with individual SQL statements. Some things you could do easily like find the leaf nodes (i.e., use a sub-select and identify nodes that nobody else uses as a parent) or find the root nodes (parent id is null) but getting a full hierarchy requires multiple SQL statements and thus is faster to process in a custom hierarchical structure rather than through SQL. Also note that using version DESC will not necessarily give you the expected results because it's using string comparison and '10.0' is less than '2.0' with string comparison. You could create a custom collation routine to do proper version number comparison but it'd be faster to store the data as separate integers (a field for major and minor version). HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Clark Christensen [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 20, 2007 1:57 PM To: SQLite List Subject: [sqlite] Finding linked peers I'm having trouble wrapping my head around a solution here. Any advice is appreciated. I'm working on a SQLite-based app for keeping track of PC BIOS releases. One obvious requirement is to be able to track and document change history. So, using this sample schema/data code: create table bios_release ( oid integer primary key, parent_id integer, group_id integer, version text, bios_name text ); insert into bios_release values (1, null, 1, '1.10', 'PC1_v1.1'); insert into bios_release values (2, null, 2, '1.10', 'PC2_v1.1'); insert into bios_release values (3, 1, 1, '1.20', 'PC1_v1.2'); insert into bios_release values (4, null, 3, '1.10', 'PC3_v1.1'); insert into bios_release values (5, 3, 1, '1.30', 'PC1_v1.3'); /* irrelevant to the discussion, but provided for clarity (?) */ create table bios_group ( oid integer primary key, group_name text ); insert into bios_group values (1, 'PC1 Group'); insert into bios_group values (2, 'PC2 Group'); insert into bios_group values (3, 'PC3 Group'); I see two ways to go to know which releases. The obvious way is to simply maintain a history group with an ID number that gets stored in each member BIOS release record. A simple: select * from bios_release where group = 1 order by version desc; will get me all the members of the PC1 Group in descending order. That's fine, but, creating, naming, and maintaining the groups is a hassle for the administrators. The other option I'm considering is chaining each new bios_release to its predecessor by storing the predecessor OID in the parent_id column. Doing it this way makes some sense from the admin process POV because usually, the admin has a new BIOS with a set of known properties, and not necessarily any knowledge of what group it might belong to, or whether or not she needs to create a new group for this BIOS. So, finally, the question: What might the SQL look like to retrieve a list of predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl), and walk the chain in some fashion. Is this result even possible using plain SQL? TIA -Clark - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: multithread problem
On 3/20/07, Jakub Ladman [EMAIL PROTECTED] wrote: Dne úterý 20 březen 2007 12:42 Igor Tandetnik napsal(a): Rafi Cohen [EMAIL PROTECTED] wrote: 1. Should I open the database explicitly in the amin part and also in the thread? In my experience, SQLite works best when every thread opens its own connection. How should I understand it? It is faster? Much secure or what? I have multithreaded program, where some threads are inserting data into tables (in random moments), two of them are retrieving subsets of data to send it via udp protocol over internet in short data length, and one thread operates as terminal for human users, where (single) user can fed sql statements and retrieve data in human readable format. The architecture looks like this: Single thread locks a common mutex just before and then calls an sqlite api, when it receives excepted data, the mutex is unlocked. Do you think that is there a better method? Thank You Jakub Ladman - To unsubscribe, send email to [EMAIL PROTECTED] - SQLite can support multiple readers to the same database, so the common mutex will be a bottle neck if two separate threads want to simply do reads from the database. In an application I am writing, I was doing the same approach as you, then I switch to using a separate database pointer per thread, and I noticed that my code just looked cleaner (since when I needed to open a database, I just created an instance of an sqlite wrapper class I wrote, and did not have to worry about sharing the mutex or any other synchronization problems). Note, that if one thread performs a write the database will be locked, so other threads may stall, if you chose to use multiple SQLITE db pointers, make sure you check your sqlite_steps/exec's for the return condition SQLITE_BUSY (and/or SQLITE_LOCKED). In general if a library multithreads well (which in my opinion sqlite 3 does) then why not take advantage of it... Instead of trying to serialize concurrent processes in your code. Let me know how it works.
[sqlite] Date/timezone problem?
I just downloaded Tcl bindings for 3.3.13 and am having trouble with
some code that I wrote last year. Here's my code:
load tclsqlite3.dll Sqlite3
sqlite3 db foo.sqb
set q1 SELECT DATETIME('now','localtime','start of day') today
db eval $q1 x {
set today $x(today)
puts today = $today
}
set dq SELECT DATETIME('$today','localtime','start of day','-1 days')
t1,
append dq DATETIME('$today','localtime','start of day','+1 days') t3
db eval $dq x {
set yesterday [lindex $x(t1) 0]
set tomorrow [lindex $x(t3) 0]
puts yesterday=$yesterday tomorrow=$tomorrow
}
Running this script, I get this:
today = 2007-03-20 00:00:00
yesterday=2007-03-18 tomorrow=2007-03-20
Seems to be off somehow. This code worked up until the day we in the US
switched over to the new timezone rules. I'm running Windows XP/Pro
fully patched and ActiveState 8.4.14.0 ...
Is there an easier way to do this? Maybe this is a bug in Tcl?
---
Nah, I just ran it on (patched) Red Hat in C. Same result.
Help?
/jordan
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Date/timezone problem?
Jordan Hayes [EMAIL PROTECTED] wrote:
I just downloaded Tcl bindings for 3.3.13 and am having trouble with
some code that I wrote last year. Here's my code:
load tclsqlite3.dll Sqlite3
sqlite3 db foo.sqb
set q1 SELECT DATETIME('now','localtime','start of day') today
db eval $q1 x {
set today $x(today)
puts today = $today
}
set dq SELECT DATETIME('$today','localtime','start of day','-1 days')
t1,
append dq DATETIME('$today','localtime','start of day','+1 days') t3
db eval $dq x {
set yesterday [lindex $x(t1) 0]
set tomorrow [lindex $x(t3) 0]
puts yesterday=$yesterday tomorrow=$tomorrow
}
Running this script, I get this:
today = 2007-03-20 00:00:00
yesterday=2007-03-18 tomorrow=2007-03-20
Seems to be off somehow
You appear to be apply the 'localtime' correction twice: once when
you initially compute today and then again before you apply
the -1 day and +1 day operators.
A date in SQLite is not an object that keeps a separate hidden
actual date behind the scenes. A date is just a string. When
you apply 'localtime' it does not set some display flag - it
actually changes the string. So you can apply 'localtime' multiple
times and it will keep shifting the date. For example, consider
this SQL:
SELECT 111, datetime('now');
SELECT 222, datetime('now','localtime');
SELECT 333, datetime('now','localtime','localtime');
SELECT 444, datetime('now','localtime','localtime','localtime');
The output is:
111|2007-03-21 01:44:03
222|2007-03-20 21:44:03
333|2007-03-20 17:44:03
444|2007-03-20 13:44:03
With that in mind, go back and remove the extra 'localtime'
modifier and I think your code will work.
--
D. Richard Hipp [EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Date/timezone problem?
A date is just a string. When you apply 'localtime' it does not set some display flag - it actually changes the string. Ok, I get it. Thanks! /jordan - To unsubscribe, send email to [EMAIL PROTECTED] -
