Re: [sqlite] Convert byte-array to in-memory DB?
Joe, Thanks for the response, and sorry for not being clear on my work flow. Ultimately, I want to access a standard SQLite database "in-memory". Starting with a disk-based SQLite database, I can easily replicate it into a memory-based database and access it from there. That's not a problem. The issue lies in my starting point. The persistent storage of my SQLite database file isn't as a separate, disk-based file as it would normally be. Instead, it's stored as a stream inside a "container file" (an MS "DocFile"). Via C#, I can retrieve the stream that is the SQLite database, which ultimately results in the stream being stored in-memory as a C# byte array. Now, in order to get where I want to be (an in-memory SQLite database), I currently save the byte array to disk which results in a standard disk-based SQLite file. From there, I can go through the steps necessary to open the disk-based file, replicate it to memory, and access it as needed. I was just hoping there might be a way to avoid the process of writing the byte-array to disk and instead point SQLite at the byte array that *is* the database. Again, I'd guess this just isn't possible, but thought I'd ask. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Convert byte-array to in-memory DB?
Hi All, I have a C# application that uses the System.Data.SQLite assembly for SQLite access. The ultimate goal is to access the SQLite data as an in-memory DB. Prior to access though, the database file itself has to be retrieved from a "container storage mechanism", as it's not stored independently on disk. Currently, that's being done by streaming the DB file from the container into a C# byte-array. From there, I can write the byte-array to the disk and then take the necessary steps to open it as an in-memory database, though I'd like to avoid the "write to disk" step if possible. Ideally, I'd like access the already-in-memory byte array as the in-memory SQLite db. Is that at all possible? I assume no, but thought I'd ask. Thanks for any input. Jeff Godfrey ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which TCL distrabution for Windows XP for running SQLite tests?
Brown, Daniel wrote: > Good afternoon list, > > If I would like to use the TCL based tests on my version of SQLite on > Windows XP what would be the recommend TCL distribution to use? Would > ActiveTCL (http://tinyurl.com/5wl6uv) be compatible with the SQLite TCL > scripts on Windows XP? > > Cheers, > > Daniel Brown | Software Engineer > "The best laid schemes o' mice an' men, gang aft agley" > That'd definitely be my recommendation. I use ActiveTcl for all of my Tcl development, which happens to be Windows XP based and often incorporates SQLite. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] seeking storage design assistance
Stephen Woodbridge wrote: > It also helps to know how you want to access/manipulate your data and > what you want to do with it in SQL versus with your application. For > instance, you could just store all the cad data as a blob, along with > the attributes in columns. Steve, Hmmm... That's an interesting thought. My initial reaction was "No, I really need direct SQL access to the coordinate data". But, thinking about that a bit more, maybe that's not really true. As you mentioned, I guess I really need to work more on the details of what I want to do with SQL and what I want to do within my application code... Thanks for the input. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] seeking storage design assistance
Jeffrey Becker wrote: > ORM is always a tricky business. My experience is that unless the > entity is very very simple, it's often inappropriate to attempt to do > 1 row = 1 entity mapping. If things are collections of points, then > by all means have a points table. A lot of this type of stuff can be > made easier by 1) really understanding your object-model first and 2) > comming up with good mapping strategies for your objects. > Jeffrey, Thanks for the input. In this case, the RAW geometry really can be defined via a series of points, though an attribute or two would be required in a few cases. For instance, I can define a basic arc with 3 points (start, center, end), but I need a direction (CW or CCW) to complete the definition. Since I already have a requirement to store other attributes, that's probably not a show-stopper. You're right though, I really need to think some more on the overall mapping strategy. Thanks again for the input. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] seeking storage design assistance
Hi All, I've got some general db storage design questions that I hope someone can offer some advice on... I have a need to store some CAD-type geometry in a SQLite database, and I'm trying to decide on the best db design. Specifically, I need to store "entity" data, which will include such things as points, lines, arcs, circles, and text data. Along with the physical coordinate data that defines each entity, I also need to store various "attributes" about each entity (color, layer, style, font (for a text object), etc)... Ideally, I'd like a single record to tell me everything about a given entity. So, one option would be to create a unique table for each entity-type in question, with columns as appropriate. While that makes sense to me on the surface, I also have the need to "step through" the geometry in an ordered fashion. Obviously, I could keep some kind of "entity order" table with references to each entity and the table it was stored in, but then I don't see a clean way to walk through the geometry. For instance, my "order" table might direct me to first get a line from the line table, then a circle from the circle table, then a text string from the text table. While this isn't too difficult to accomplish via program logic, it seems a bit "messy", which has me wondering if there might be a better way. Since the table for each unique entity type would contain (at least some) columns unique to the specific entity, I don't think there's a way to combine the tables into a single, ordered view that could be easily "walked", is there? The other thought I had was to create a simple "POINT" table, and store all the points that make up every entity in that one table. Then, I'd need a way, per entity, to reference which points belonged to the current entity. So, a line would reference 2 records in the POINT table, an arc would reference 3 POINT records, etc. One obvious drawback to this approach is that now there's not a single record that contains an "entire" entity, as there would be in the first approach. Since I'm not an expert in this arena, I'm hoping that I'm missing an obvious solution. Any thoughts appreciated. Also, if you need further clarification on any of the above, feel free to ask. Thanks for any input. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] best language match for SQLite?
Patrick wrote: > I was just wondering if anyone had an opinion on the most ideal language > to use with SQLite? > > I love Python but I LOVE SQLite, I would learn another language just to > use it better-Patrick > According to a paper written by Richard Hipp (the creator of SQLite), Tcl is the ideal language. Here's a quote from the mentioned paper: "The increasing popularity of SQLite is seen in the fact that the main website daily serves about a gigabyte of data to around 3000 unique IP addresses. SQLite has been eagerly embraced by PHP, Perl, and Python programmers. What most of these enthusiastic users fail to realize is that SQLite bindings for the three P-languages are an afterthought. SQLite was designed from the beginning to be used with Tcl. Tcl bindings have been in the SQLite core since before version 1.0 and almost half of the SQLite source code base consists of regression test scripts written in Tcl. SQLite wants to be programmed in Tcl, not those other languages." The entire paper can be found here: http://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Grabbing a record from an "in-use" database via a second process...
Hi All, I'm writing a Tcl-based application that manages a pool of separate SQLite database files. The application is multi-User, but it requires each User to "check out" a specific database in order to access it, so a single database is only ever accessed by a single User That is, until now... ;^) I have one specific situation where I need to retrieve a single BLOB record from another database in the pool (not the one that's currently checked out). In fact, this other database could be checked out to (and be in use by) another individual when I need the record. So, my question. Is it safe for me to blindly open the other database and grab the record I need? This would only be a SELECT operation, and would never actually write anything to the other database file. If that's deemed unsafe, could I simply make a temporary copy of the other database (literally by copying the db file itself), grab the record from there, and remove the copy? In case it's important, the application will be running on WinXP... Thanks for any advice. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Upgrade from 3.4.2 to 3.5.9 breaks Tcl code
D. Richard Hipp wrote: > On Jun 13, 2008, at 12:22 AM, Jeff Godfrey wrote: > >> Thanks for the quick solution. I've adjusted my code accordingly. >> So, >> do you consider this a buglet or more just the closing of a gap that >> shouldn't have existed in the first place? > The column naming algorithm for results > without an AS clause has changed in the past and will probably change > again in the future. So make no assumptions. Always use an AS clause. > Noted for future reference, and code (re)adjusted... ;^) Thanks, Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Upgrade from 3.4.2 to 3.5.9 breaks Tcl code
D. Richard Hipp wrote:
> On Jun 12, 2008, at 11:08 PM, Jeff Godfrey wrote:
>
>> dbMem eval {select distinct(owner) from lockinfo order by owner} {
>>
>> }
>>
>> That works correctly in my original app using 3.4.2, but complains
>> that
>> there is no such variable "owner" when using 3.5.9.
>>
>
> Solution 1:
>
>dbMem eval {SELECT DISTINCT owner FROM lockinfo ORDER BY owner} {
> puts $owner
>}
>
> Solution 2:
>
>dbMem eval {SELECT DISTINCT(owner) AS owner FROM lockinfo ORDER BY
> owner} {
>puts $owner
>}
>
> Solution 3:
>
>dbMem eval {SELECT DISTINCT(owner) FROM lockinfo ORDER BY owner} {
> puts ${(owner)}
>}
>
Thanks for the quick solution. I've adjusted my code accordingly. So,
do you consider this a buglet or more just the closing of a gap that
shouldn't have existed in the first place? It's interesting that the
original code works outside of a proc in 3.5.9, but not inside...
Again - thanks.
Jeff
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Upgrade from 3.4.2 to 3.5.9 breaks Tcl code
Hm... I sent this message last night as a non-list member. I
received a notification that it would need to be approved by a moderator
before being made public. About 15 hours later, I received a "Post was
rejected... No reason given" message. Why's that? So now I'm
subscribed again. Anyway...
==
Hi All,
I am upgrading a Tcl/SQLite based application from SQLite 3.4.2 to
SQLite 3.5.9. I'll mention that I'm also upgrading from Tcl 8.4.19 to
Tcl 8.5.2 in case it's relevant, though both versions of Tcl behave the
same when using SQLite 3.5.9.
Anyway, I have a number of procedures that operate like the following:
dbMem eval {select distinct(owner) from lockinfo order by owner} {
}
That works correctly in my original app using 3.4.2, but complains that
there is no such variable "owner" when using 3.5.9. Interestingly, I've
found that the above code works under 3.5.2 when not inside a Tcl
procedure. Inside the procedure though, it breaks as described. Also,
a few other variations...
Inside a procedure, using 3.5.2, replacing "distinct(owner)" with just
"owner" works correctly and replacing "distinct(owner) with just "*"
works correctly, though neither obviously does what I need.
Is this an intended change to SQLite Tcl bindings, a bug, or just
something else I'm missing?
Thanks for any insight.
Jeff Godfrey
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-User confusion
Joe Wilson wrote: --- Jeff Godfrey <[EMAIL PROTECTED]> wrote: Can you (or anyone else) point me to some web-based information? http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/os_unix.c&v=1.165 Joe, Thanks for the pointer. I should have mentioned, my application is running under Windows (Win2000 and WinXP). A quick look at the mentioned code makes me believe that it targets Unix-only systems (though there are a few brief mentions of Windows, such as "The algorithms are complicated slightly to be compatible with Windows..."). Do you know if the mentioned dotLockLockingStyle is compatible with a Windows environment? Thanks again. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
[EMAIL PROTECTED] wrote: You may have a look at the dhRCPServer at: http://www.thecommon.net/2.html I am not using it, but it sounds it may do the job. RBS Bart, Thanks for the pointer. It does sound quite interesting, though I don't know if it can (easily?) be used from within a Tcl-based application. I'll have to do some research... Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
Joe Wilson wrote: If your database storage device cannot guarantee an exclusive file lock, then any database write can potentially result in corruption. If you control all SQLite clients' code, you could recompile sqlite to use the file-based dotlockLockingStyle convention via -DSQLITE_ENABLE_LOCKING_STYLE=1 But even if a single client does not use that locking convention, you still risk corruption. Joe, Thanks for the input. I absolutely control all of the SQLite clients - they are just unique instances of my (Tcl-based) application. I am unaware of the mentioned "dotlockLockingStyle" convention. Can you (or anyone else) point me to some web-based information? Thanks again. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Multi-User confusion
Hi All, I currently have a single-user SQLite-based application that, due to customer need, is being pushed toward multi-user access. I've done some research on the multi-user capabilities of SQLite. It seems the general consensus is that when the database file is stored on a network drive (as is my case), the integrity of the stored data becomes questionable (apparently due to bugs in the various NFS file locking protocols). Fortunately, my application is designed such that (generally speaking) each User of the system will be working within their own SQLite database. However, there are a few select places in the code where a User could trigger an action that would cause the storage of data to a common, upper-level SQLite database. I think I can change portions of the application to ensure that these common writes never happen concurrently, but I'd like to understand the underlying situations and dangers that can occur in this environment. So, what are the cases that could cause database corruption? 1. Multiple Users writing to the same table of the same open database at the same time? 2. Multiple Users writing to two different tables of the same open database at the same time? 3. Multiple Users writing to the same table of the same open database at different times? 4. Other cases I haven't thought about? I realize there also some dangers with regard to potentially writing "stale" data to the database thus losing someone else's updates. I still have some thinking to do in that regard, but for now I'd like to understand the situations that could compromise the integrity of the underlying database file itself. Thanks for any details you can provide. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL query assistance...
- Original Message - From: "Dan Kennedy" <[EMAIL PROTECTED]> To: Sent: Monday, July 02, 2007 4:17 AM Subject: Re: [sqlite] SQL query assistance... On Sun, 2007-07-01 at 16:31 -0500, Jeff Godfrey wrote: Hi All, Given the following sample data... ID Name Version --- - --- 1 name1 0.9 2 name1 1.0 3 name2 1.2 4 name3 1.0 5 name3 1.7 6 name3 1.5 I need to create a query that will group the data together by Name, but for each group will return the record with the highest version number and a count of the items in the group. So, with the above data, I'd expect this as output: ID Name Version Count --- - --- - 2 name1 1.0 2 3 name2 1.2 1 5 name3 1.7 3 Thanks for any assistance. How about: SELECT tbl.id, grp.name, grp.c, grp.v FROM (SELECT name, count(*) AS c, max(version) AS v FROM tbl GROUP BY name ) AS grp, tbl WHERE grp.name = tbl.name AND grp.version = tbl.version; I think the question only makes sense if the combination of name and version are unique in the table. Dan, Thanks for that. Yes, each "name/version" combo are unique in the table. Except for a few minor naming mismatches, the above provided query works perfectly. Thanks again. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL query assistance...
- Original Message - From: "Jeff Godfrey" <[EMAIL PROTECTED]> Thanks Gerry, Indeed, I have tried exactly that. Hmmm... This seems to be a thread for eating crow... ;^) Hi Gerry, My actual query is a bit more complex than the sample I posted. I thought my posted sample was a valid representation of what I'm actually trying to do (and I still do), though I had trouble with the "ID" and "Version" in the result set being constructed from differing records. I've added a table containing exactly the data I showed in my example, and indeed, the query you posted does seem to work exactly as I requested. So, I seem to have failed in my efforts to accurately "simplify" the problem. Now, I need to figure out what's different between my real situation and the one I proposed here. Thanks again for you assistance. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL query assistance...
- Original Message - From: "Andrew Finkenstadt" <[EMAIL PROTECTED]> To: Sent: Sunday, July 01, 2007 4:45 PM Subject: Re: [sqlite] SQL query assistance... On 7/1/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote: Hi All, Given the following sample data... ID Name Version --- - --- 1 name1 0.9 2 name1 1.0 3 name2 1.2 4 name3 1.0 5 name3 1.7 6 name3 1.5 I need to create a query that will group the data together by Name, but for each group will return the record with the highest version number and a count of the items in the group. So, with the above data, I'd expect this as output: ID Name Version Count --- - --- - 2 name1 1.0 2 3 name2 1.2 1 5 name3 1.7 3 Thanks for any assistance. select ID, Name, max(version), count(*) Count from table_name group by ID, Name order by ID, Name ... that looks to meet your criteria. Hmmm... Looking closer, this doesn't seem to be right. Sorry for my earlier report - I didn't look at the data close enough. Since ID is always unique, won't GROUP BY ID effectively eliminate any possible grouping? Sorry, still learning here... Thanks, Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL query assistance...
- Original Message - From: "Andrew Finkenstadt" <[EMAIL PROTECTED]> To: Sent: Sunday, July 01, 2007 4:45 PM Subject: Re: [sqlite] SQL query assistance... On 7/1/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote: Hi All, Given the following sample data... ID Name Version --- - --- 1 name1 0.9 2 name1 1.0 3 name2 1.2 4 name3 1.0 5 name3 1.7 6 name3 1.5 I need to create a query that will group the data together by Name, but for each group will return the record with the highest version number and a count of the items in the group. So, with the above data, I'd expect this as output: ID Name Version Count --- - --- - 2 name1 1.0 2 3 name2 1.2 1 5 name3 1.7 3 Thanks for any assistance. select ID, Name, max(version), count(*) Count from table_name group by ID, Name order by ID, Name ... that looks to meet your criteria. Andrew, Indeed, the above does seem to do exactly what I need. I guess the missing link (in my experimentation), was adding ID to the GROUP BY step. Without that, the returned ID and Version weren't guaranteed to come from the same original record. It's always so easy when you see it done... Thanks for your assistance. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL query assistance...
- Original Message - From: "Gerry Snyder" <[EMAIL PROTECTED]> To: Sent: Sunday, July 01, 2007 4:40 PM Subject: Re: [sqlite] SQL query assistance... Jeff Godfrey wrote: Hi All, Given the following sample data... ID Name Version --- - --- 1 name1 0.9 2 name1 1.0 3 name2 1.2 4 name3 1.0 5 name3 1.7 6 name3 1.5 I need to create a query that will group the data together by Name, but for each group will return the record with the highest version number and a count of the items in the group. So, with the above data, I'd expect this as output: ID Name Version Count --- - --- - 2 name1 1.0 2 3 name2 1.2 1 5 name3 1.7 3 Thanks for any assistance. It would seem that something like: select ID, Name, max(Version),count(*) from table group by Name should work. Thanks Gerry, Indeed, I have tried exactly that. The problem is that max(Version) just returns the maximum version for the group, right? So, the returned version doesn't necessarily "belong" to the ID that's returned, correct? The result should be exact copies of the original records that had the highest version for each group, along with a count for each group. The above query seems to mix the "highest version" with whatever record was chosen to represent the group. I need to ensure that the group record *is* the record with the highest version... Thanks, Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQL query assistance...
Hi All, Given the following sample data... ID Name Version --- - --- 1 name1 0.9 2 name1 1.0 3 name2 1.2 4 name3 1.0 5 name3 1.7 6 name3 1.5 I need to create a query that will group the data together by Name, but for each group will return the record with the highest version number and a count of the items in the group. So, with the above data, I'd expect this as output: ID Name Version Count --- - --- - 2 name1 1.0 2 3 name2 1.2 1 5 name3 1.7 3 Thanks for any assistance. Jeff
Re: [sqlite] Tcl and BLOB data
Very informative. Thank you. Jeff - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Tuesday, June 19, 2007 8:59 PM Subject: Re: [sqlite] Tcl and BLOB data "Jeff Godfrey" <[EMAIL PROTECTED]> wrote: I don't really understand why, when my data has a binary representation and I'm trying to place it in a BLOB field, the string representation is used instead (assuming it exists). I wrote up a terse explanation on the Tclers wiki. See http://wiki.tcl.tk/19627 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tcl and BLOB data
- Original Message - From: <[EMAIL PROTECTED]> To: Sent: Tuesday, June 19, 2007 3:43 PM Subject: Re: [sqlite] Tcl and BLOB data I did this change at your request, because it seemed like a good enhancement. Thanks. I agree, it sounds like a good enhancement. Though, I don't really understand why, when my data has a binary representation and I'm trying to place it in a BLOB field, the string representation is used instead (assuming it exists). It seems that the fact I'm pushing the data into a BLOB field should force the selection of the binary representation. That said, I'm sure I'm over-simplifying the situation. How can we help you to do your own build? It really is not that hard. What operating system are you using? Hmmm... Maybe point me to some docs. I seem to remember seeing some on the Wiki before. In reality, I *have* tried to build the package before (quite some time ago), and don't think I had any luck. I running on WinXP, though I have mingw, msys, and tcl installations. I'll give it another try if there's some current documentation available... Thank you. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tcl and BLOB data
Interesting. That sounds like exactly what I need. I'm curious, did the patch somehow arise from my query, or is the timing of the query and the patch just coincidental? Also, how soon would you expect this patch to make it into an "official build"? I've never built SQLite from the sources before, and don't really have the time to work through the details right now. Thank you. Jeff Godfrey - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Tuesday, June 19, 2007 12:17 PM Subject: Re: [sqlite] Tcl and BLOB data "Jeff Godfrey" <[EMAIL PROTECTED]> wrote: Is there a way I can "force" SQLite to insert my data as a BLOB, even if the containing variable has a string representation? I tried to CAST the data to a BLOB during the insert, but the results were the same. If you get the patch I just checked in http://www.sqlite.org/cvstrac/chngview?cn=4092 and if you use a "@" character instead of "$" in front of the variable name, and the variable has a bytearray representation, then the variable will be bound as a BLOB even if the variable also has a text representation. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Tcl and BLOB data
Hi All, I have an interesting Tcl / SQLite BLOB issue going on that I hope you can help with... I have binary data stored in a Tcl variable that I'm stuffing into SQLite BLOB field. The resulting data in the BLOB field is corrupted. After much experimentation, and help from the folks on comp.lang.tcl, it seems that the issue is related to whether or not the Tcl variable in question contains a string representation at the time it's inserted into the BLOB field. If it does contain a string representation, the inserted data is incorrect. If it does not contain a string representation, the inserted data is correct. Not wanting to get into a "Tcl Internals" discussion here, my question is this: Is there a way I can "force" SQLite to insert my data as a BLOB, even if the containing variable has a string representation? I tried to CAST the data to a BLOB during the insert, but the results were the same. I have several potential solutions from the Tcl side, but wanted to explore the possibilities from this side also. Thanks for any input. Jeff Godfrey
Re: [sqlite] SQL query help
- Original Message - From: "P Kishor" <[EMAIL PROTECTED]> To: Sent: Monday, June 18, 2007 2:55 PM Subject: Re: [sqlite] SQL query help On 6/18/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote: I have a table which contains (among other things), a "name" column and a "version" column (a software asset table). I need a query that will group all like "names" together in a single record, and return the latest "version" (the largest value) for each group. What I have Jeff, how about something like SELECT name, MAX(version) AS latest FROM asset GROUP BY name Thanks Puneet - that's just what I needed. Side note - the timing on the list seems to be whacky right now (at least for me), so some of my responses seem to be coming out of order. Sorry if that causes some confusion... Thanks again. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQL query help
- Original Message - From: "Igor Tandetnik" <[EMAIL PROTECTED]> To: "SQLite" Sent: Monday, June 18, 2007 1:17 PM Subject: [sqlite] Re: SQL query help Jeff Godfrey <[EMAIL PROTECTED]> wrote: I have a table which contains (among other things), a "name" column and a "version" column (a software asset table). I need a query that will group all like "names" together in a single record, and return the latest "version" (the largest value) for each group. select name, max(version) from asset group by name; Igor Tandetnik Igor, Perfect - thank you. It's so simple once you see it done... ;^) Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQL query help
Not seeing this on the list 1.5 hrs after posting, I thought I'd try again. Sorry if this is a duplicate... Jeff = Hi All, I have a table which contains (among other things), a "name" column and a "version" column (a software asset table). I need a query that will group all like "names" together in a single record, and return the latest "version" (the largest value) for each group. What I have so far is this: SELECT name, version FROM asset GROUP BY name ORDER BY name ASC, version DESC While the above seems to return the expected results, I'm not convinced that I'm actually controlling the sort order, as changing "version DESC" to "version ASC" does not return the *earliest* version as I'd expect. I assume the record that will be returned has already been selected at the "GROUP BY" stage and therefore I have no control over it at the "ORDER BY" stage? I know, I need to do some more reading... ;^) Thanks for any input. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQL query help
Hi All, I have a table which contains (among other things), a "name" column and a "version" column (a software asset table). I need a query that will group all like "names" together in a single record, and return the latest "version" (the largest value) for each group. What I have so far is this: SELECT name, version FROM asset GROUP BY name ORDER BY name ASC, version DESC While the above seems to return the expected results, I'm not convinced that I'm actually controlling the sort order, as changing "version DESC" to "version ASC" does not return the *earliest* version as I'd expect. I assume the record that will be returned has already been selected at the "GROUP BY" stage and therefore I have no control over it at the "ORDER BY" stage? I know, I need to do some more reading... ;^) Thanks for any input. Jeff
Re: [sqlite] SQL query help...
- Original Message - From: "Trey Mack" <[EMAIL PROTECTED]> To: Sent: Friday, June 08, 2007 1:08 PM Subject: Re: [sqlite] SQL query help... Here's what I tried, which didn't work... select name, substr(name,1,length(name)-3) as zone, substr(name,length(name)-2,2) as location, max(thick) - min(thick) as diff from plypoint where diff > 0.0005 group by zone,location That causes a "misuse of aggregate" error. select name, substr(name,1,length(name)-3) as zone, substr(name,length(name)-2,2) as location, max(thick) - min(thick) as diff from plypoint group by zone,location having diff > 0.0005; Trey, That seems to work great. I appreciate the assistance - thank you. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQL query help...
Hi All, I need a little help in constructing a SQLite query.. Here's what I have so far that works... select name, substr(name,1,length(name)-3) as zone, substr(name,length(name)-2,2) as location, max(thick) - min(thick) as diff from plypoint group by zone,location The above properly calculates a range value for a group of records and returns the groups accordingly. Now, what I need to do is only return records where the calculated range value exceeds some value (say, 0.0005). Here's what I tried, which didn't work... select name, substr(name,1,length(name)-3) as zone, substr(name,length(name)-2,2) as location, max(thick) - min(thick) as diff from plypoint where diff > 0.0005 group by zone,location That causes a "misuse of aggregate" error. Thanks for any assistance. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] What is wrong with this simple query (offset)?
- Original Message - From: "RB Smissaert" <[EMAIL PROTECTED]> To: Sent: Tuesday, March 06, 2007 1:39 PM Subject: [sqlite] What is wrong with this simple query (offset)? Why does this query give a syntax error near offset? SELECT Name FROM SQLITE_MASTER WHERE TYPE = 'table' ORDER BY 1 ASC offset 2 limit works fine. Though I've never used OFFSET, the documentation seems to state that OFFSET is an *optional* component of the LIMIT clause. So, I'd guess you can't use it without also using LIMIT. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: sqlite / tcl syntax help
Michael / Richard / Dennis, Thanks for the additional input. Problem fixed... Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite / tcl syntax help
- Original Message -
From: "Michael Schlenker" <[EMAIL PROTECTED]>
set dataType [$db onecolumn {select typeof($colName) from $table}]
should do what you want. Note the {} instead of the "", which
prevent early substitution, so sqlite can use the $colName as a bind
variable.
Michael
Hi Michael,
Yeah, I agree that makes sense from a Tcl standpoint. In fact, that's
exactly what I started with. Unfortunately, it doesn't work as
expected and just returns the following:
Error: near "$table": syntax error
So, the curly-braces are preventing the Tcl parser from substituting
the vars (as they should), but then SQLite doesn't substitute the
$table var for some reason. I guess I'm a bit unclear on exactly
*what* SQLite is capable of substituting with regard to Tcl variables.
It only seems capable of substituting variables in certain cases, or
certain locations, or ??? I'm not sure.
I've found that if my table name is variable, I need for Tcl to
substitute it instead of SQLite - which lead me to my original
problem. Is SQLite's substitution working as expected? If so, can
someone point to more documentation regarding SQLite's Tcl var
substitution?
Thanks,
Jeff
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: sqlite / tcl syntax help
> So, how can I get the correct column types returned for all > columns, while at the same time properly handle column > names containing spaces? > Thanks for any insight. > Jeff Answering my own post, I just found that the following works as expected... set dataType [$db onecolumn "select typeof(\"$colName\") from $table"] I'm OK with that, but if anyone has a suggestion for a more preferred syntax, I'm listening. Also, should space-separated column names "just work" as an argument to "typeof". That is, is this a bug? Thanks, Jeff
[sqlite] sqlite / tcl syntax help
Hi All,
I'm trying to determine the datatype of a given column using Tcl and the
following code snippet...
set dataType [$db onecolumn "select typeof($colName) from $table"]
This works correctly as long as $colName (the name of the current column)
doesn't contain a space. When the column name contains a space (such as
"ASSEMBLY NUMBER"), the above code fails with:
Error: near "NUMBER": syntax error
Obviously, the "NUMBER" text depends on the column name. Now, that's easily
fixed by wrapping the variable name in single-quotes, like this:
set dataType [$db onecolumn "select typeof('$colName') from $table"]
While that fixes the issue with the space, the single-quote wrapper causes the
"typeof" function to return "text" for all column types, which is incorrect.
So, how can I get the correct column types returned for all columns, while at
the same time properly handle column names containing spaces?
Thanks for any insight.
Jeff
Re: [sqlite] SQLite in Adobe Lightroom
- Original Message - From: "Eric Scouten" <[EMAIL PROTECTED]> To: Sent: Thursday, February 22, 2007 10:55 AM Subject: Re: [sqlite] SQLite in Adobe Lightroom As the Adobe engineer who did much of the work to embed SQLite into Lightroom, I do want to express a couple of cautions about directly manipulating your Lightroom library. Eric, It's good to see an official Adobe representative on the list... ;^) I understand and appreciate your comments. In my case, I would only (possibly) have an interest in *reading* data from db. I am an Pixmantec RSP user (which was recently absorbed by Adobe and then dead-ended) and have desparetely been trying to get Adobe to release the format of RSP's proprietary ".RWS" file so I can recover some of the work I've put into (my over 10-thousand) RSP RAW conversions. While that's looking unlikely to happen (though I'm still holding out some hope), I do see that Adobe plans to provide an RSP to Lightroom conversion tool. After noticing that LR uses SQLite, I am now hoping that the converted RWS settings will be stored in the SQLite database, which will hopefully make them more accessible to me (for use in other parts of my DAM workflow) than they currently are locked away in the binary RWS file. Is it safe to assume that conversion settings will be stored in the SQLite database? I don't suppose I we'll see any public schema documentation on the database content, will we? Thanks for any additional insight. Also, if this is deemed too far off topic for the list, I'll be happy to take it offline. Thanks, Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite in Adobe Lightroom
- Original Message - From: <[EMAIL PROTECTED]> To: Sent: Thursday, February 22, 2007 6:37 AM Subject: Re: [sqlite] SQLite in Adobe Lightroom "Jeff Godfrey" <[EMAIL PROTECTED]> wrote: > Though it's only a matter of curiosity, I wonder if anyone > here knows how/where Adobe employed SQLite in the Lightroom > product? Adobe stores just about all of your Lightroom state in an SQLite database. Find the database (on your Mac) at Thanks for the info. I am using the Windows version of LR, and for those interested, the database file is located here: \\My Documents\My Pictures\Lightroom\Lightroom Database.lrdb Seems to be just a standard SQLite database, viewable using the tool of your choice. As a programmer / amateur photog, this is *very* interesting... ;^) Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Another sybase conversion question
- Original Message - From: "Rich Shepard" <[EMAIL PROTECTED]> But, if you're going to do much with SQLite, I strongly recommend Mike Owens' "The Definitive Guide to SQLite" by Apress. The index is shamefully bad, but the book is a gem and has been a great help to me. Very highly recommended -- despite the index. :-) Agree 100%. I have the book and find it a great resource. Interesting that you should mention the index... It's the most worthless thing I've seen in a technical book in a long time. A shame really... In fact, the book is so good and the index is so bad that I purchased the $10 e-Book version also just so I could do text searches... Maybe that was the plan all along? ;^) Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "select into" ?
- Original Message - From: "Anderson, James H (IT)" <[EMAIL PROTECTED]> Sybase supports "select into" but sqllite does not seem to. Here's a sybase example: select distinct CDId, CDEvent into credDerivEvent from credDerivOrig This query creates the table credDerivEvent. Is there anythin comparable in sqlite? I'm no expert, but how does that differ from "insert into ??? select ??? from ???", which is supported by sqlite? Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Sqlite - tcl "trace" method
Hi All,
I'm working on a tcl/sqlite based app. For the first time, I just registered a
callback with the trace method so I could "see" all of my SQL interactions as
they happen. While it does work as advertised, it seems to output each SQL
statement prior to having resolved any variables found in the statement. For
instance, when the following SQL statement...
dbPart eval {UPDATE OR REPLACE property SET value = $status WHERE key =
'status'}
... hits my registered trace command, the "$status" variable has not yet been
resolved, so it just outputs a literal "$status". It would seem to be much
more helpful if the variable were resolved prior to firing the trace callback.
Though I haven't tried it, I assume that replacing the curly-braces in my
original statement with double-quotes would fix the problem, as then the Tcl
parser would resolve the variable prior to executing the command. I haven't
been coding my SQL statements like that as I was under the impression that the
above is a more accepted style (and maybe even faster?), though I don't know
where I found info to that effect.
So, should the TRACE command work differently in this case or should I change
my SQL-coding style? Thoughts?
Thanks,
Jeff
Re: [sqlite] sqlite core function question
From: "T&B" <[EMAIL PROTECTED]> Hi Jeff, I've encountered some functions that apparently aren't supported by SQLite So have I, such as replacing occurrences of an inner string. so I've created my own I've yet to figure out/try that. Is there a library somewhere of prebuilt functions we can add? Hi Tom, I'm not aware of any "prebuilt function library". In my case, I'm developing my app in Tcl. There creating and registering new SQL functions is simple - trivial even. So, whenever I run into a non-supported function in the SQL I'm porting, it's quick/easy to just replace it with one of my own. The biggest drawback I've found to the custom functions is the fact that they are (obviously) not available to any of the 3rd party tools I use to view/browse my raw database files with. In that case, the unknown function calls just generate errors. [... several suggestions for emulating an INT function...] Thanks for the suggestions. You've made some quite inventive attempts there... ;^) Since I've already provided my own INT function, I'm beyond that issue right now. Ultimately, I should probably go back and rewrite my queries to use CAST as DRH mentioned. Thanks, Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite core function question
From: <[EMAIL PROTECTED]> I'm not sure what "int()" does. Maybe you are looking for round(). Or perhaps cast(expr AS int) will serve your needs. Sorry, I should have been clearer. INT just forces the result to be an integer. So, your "cast" example is probably what I need. Thanks for the quick response. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite core function question
I'm currently converting some Access tables/views to SQLite. I've encountered some functions that apparently aren't supported by SQLite, so I've created my own (a power function and an "IIF" function). Also, several of my queries have a basic int() wrapper, that also seems to be unsupported. Like the others, I've just added my own, but I wonder if I'm missing something. The "expression" page doesn't seem to document an "int" function, but I wonder if there is some other equivalent? Thanks, Jeff
Re: [sqlite] Creating a view on an ATTACHed database
From: <[EMAIL PROTECTED]> "Jeff Godfrey" <[EMAIL PROTECTED]> wrote: So, is it not possible to create a view across a "main" and an "attached" database? If I recall, you can create a TEMP VIEW across attached databases. Thanks for the tip. Adding TEMP is all it took to get things working. Out of curiosity though, is there a reason why such a view can't be stored permanently? Obviously, it can't be "used" until the other table(s) are attached, but being able to store it would seem to make things a bit tidier... I may be way off base - just wondering... Again - thanks. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Creating a view on an ATTACHed database
Hi All, I have an open sqlite3 database (name = dbSerial), to which I've attached a 2nd database (name = dbParent). Now, I'm trying to create a view by joining a view from dbSerial with another view from dbParent. Attempting to create the view generates the following error: Error: view [tcoverage] cannot reference objects in database dbParent Specifically, here's my (contrived) view creation code... SELECT c.zone, t.zone FROM precoverage AS c INNER JOIN dbParent.target AS t ON (c.zone = t.zone) So, is it not possible to create a view across a "main" and an "attached" database? If not, what's my best option (copy the necessary data to a single (in memory?) database?)... Thanks for any pointers. Jeff
Re: [sqlite] Mathematical "power" operator?
- Original Message - From: "Nuno Lucas" <[EMAIL PROTECTED]> To: Sent: Thursday, December 21, 2006 5:45 PM Subject: Re: [sqlite] Mathematical "power" operator? Just wanted to add that if one of the sqlite GUI's out there uses an external sqlite dll (instead of static linking) it's quite easy to make your own dll with your own functions and replace the one that comes with the GUI application. Nuno, Thanks for the nudge. I never thought to check for the external DLL, but checking my SQLite Expert installation folder I see... sqlite3.dll Interestingly, the DLL has the same size and date/time stamp as the one that's available for download on the sqlite.org download page. Hmmm ;^) Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
All, Thanks for the interesting responses. I think I now have a clear understanding of my options, and while not exactly what I was looking for, I can work within the prescribed limitations. As I mentioned, I am working from Tcl, where it's quite easy to write and register a new function with SQLite - which I've done. So, I now have a view containing references to my new "pow" (mathematical power) function, which works exacty as expected from within my own application. Unfortunately, that view now (obviously) causes problems when using 3rd party GUI db manager tools. From here, I think it's just a matter of reorienting my take on the whole problem. As someone already mentioned, SQLite is not intended to be a stand-alone database application. Once I get that fact drilled into my head, I'll be fine... ;^) I will mention that the author of "SQLite Expert" is looking into possible solutions to this specific issue (related to using the new "loadable extensions" feature of SQLite). If anything interesting comes from those discussions, I'll let the list know. Thanks again. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Julien, Thanks for the pointer. That does indeed look interesting, though I'm not sure it completely solves my problem (though, there may be no good solution to my problem). It would seem that, using the "loadable extension" feature, I could easily create a compiled version of my necessary "power" function. Further, it would seem my new function could be loaded via a SQL statement, which *might* make it available in a 3rd party GUI package that's compiled with the standard SQLite library. That said, there seems to be one potential hiccup... According to the mentioned docs, this functionality is turned *off* by default in the library, as there are some potential security concerns. So, unless the 3rd party tool is compiled with this functionality turned *on*, I don't see that I could load my function there anyway. There may be other things that would prevent me from loading an external function into a 3rd party app also - I don't know. So, I've opened a discussion with the author of my chosen SQLite GUI tool (SQLite Expert Personal) to see what my options are. Anyway, thanks for pointing me to another possible option. As I said earlier, I'm very new to SQLite. Most of my database work (though not extensive anyway) has been with commercial packages (MS Access, Oracle, SQL Server, etc). In those environments (generally), since the package already *has* a GUI-based management tool built-in, any custom SQL functions that are created are accessible to the GUI tool by default. In the case of SQLite, I (arguably) have to use a 3rd party management tool, for which my custom functions are no longer available. I'm curious how others handle this. A. You don't need or use any custom SQL functionality B. You don't use a 3rd party SQLite management tool C. Something else I haven't thought of? Thanks for any additional guidance. Jeff Godfrey - Original Message - From: "jt" <[EMAIL PROTECTED]> To: Sent: Wednesday, December 20, 2006 9:23 AM Subject: Re: [sqlite] Mathematical "power" operator? Hi, Look at the new "loadable extension" feature: http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions It also describe an example that could be really easy to convert to your sqrt problem. -- Julien - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Jesús,
Thanks for the response. As I mentioned in my original message, it
seems that I can create my own replacement functions and register them
with SQLite via the provided "function" method. While I assume
that'll solve the issue when running my code, how does one go about
using views containing custom functions in 3rd party tools (in my case
"SQLite Expert Personal")? I assume the answer is " you don't, unless
the 3rd part tool somehow allows for the inclusion of user-defined
functions". I want to have my cake and eat it too... ;^)
Now, I guess that brings me back to my initial thought, which was...
With all the fancy functionality that's apparently supported by SQLite
expressions, isn't the absence of a basic mathematical "power"
operator a glaring omission? It seems to me that it is, though I
admittedly have very little experience in this domain.
Again, thanks for your input.
Jeff
- Original Message -
From: "Jesús López" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, December 19, 2006 3:08 PM
Subject: RE: [sqlite] Mathematical "power" operator?
Hi Jeff,
I'm not a Tcl user. I use C# and Robert Simpson's SQLite ADO.NET 2.0
provider to access SQLite databases.
SQLite allows you to define both scalar and aggregate user defined
functions. I think the way to approach your issue is to define a
scalar user
defined function. I don't know how to do it with Tcl, I just know how
to do
it with c#.
Regards:
Jesús López
-Mensaje original-
De: Jeff Godfrey [mailto:[EMAIL PROTECTED]
Enviado el: martes, 19 de diciembre de 2006 21:45
Para: sqlite-users@sqlite.org
Asunto: [sqlite] Mathematical "power" operator?
Hi All,
New to SQLite, so bear with me... ;^)
I'm trying to migrate an MS-Access database over to SQLite. I have a
VIEW
created from a SELECT statement that uses the mathematical "power"
operator
("^") for both "square root" and "squared" operations. It seems that
SQLite
doesn't support the "^" operator, so I'm trying to find the best way
around
that. I'm using SQLite from Tcl. I know that I can create my own
Tcl-based
replacements for these functions and register them with SQLite via the
"function" method, though that still seems to leave an issue.
I am using a 3rd part tool (SQLite Expert Personal) to create,
manipulate,
and generally experiment with my SQLite database. Obviously, if I
create a
Tcl-based function replacement, I can use it from within my code, but
it
won't be recognized when I open up the VIEW query that uses it via the
3rd
part tool.
Also, I can fairly easily change the query to get by without the need
for
the "squared" function, though the "square root" function would seem
to be a
bit more tricky to "code around". So, a few questions:
1. Thoughts on my specific issue with the missing mathematical
operator?
2. More generally, do people who "add" functions to SQLite just not
use 3rd
party tools to work with their data?
Thanks for any insight...
Jeff Godfrey
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Is there a method for doing bulk insertion?
Take a look at the ".separator" command. It seems to be what you need... Jeff - Original Message - From: "Anderson, James H (IT)" <[EMAIL PROTECTED]> To: Sent: Tuesday, December 19, 2006 2:52 PM Subject: RE: [sqlite] Is there a method for doing bulk insertion? So I can assume that there's no way to use a delimiter other than a comma to import a CSV file? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Mathematical "power" operator?
Hi All,
New to SQLite, so bear with me... ;^)
I'm trying to migrate an MS-Access database over to SQLite. I have a VIEW
created from a SELECT statement that uses the mathematical "power" operator
("^") for both "square root" and "squared" operations. It seems that SQLite
doesn't support the "^" operator, so I'm trying to find the best way around
that. I'm using SQLite from Tcl. I know that I can create my own Tcl-based
replacements for these functions and register them with SQLite via the
"function" method, though that still seems to leave an issue.
I am using a 3rd part tool (SQLite Expert Personal) to create, manipulate, and
generally experiment with my SQLite database. Obviously, if I create a
Tcl-based function replacement, I can use it from within my code, but it won't
be recognized when I open up the VIEW query that uses it via the 3rd part tool.
Also, I can fairly easily change the query to get by without the need for the
"squared" function, though the "square root" function would seem to be a bit
more tricky to "code around". So, a few questions:
1. Thoughts on my specific issue with the missing mathematical operator?
2. More generally, do people who "add" functions to SQLite just not use 3rd
party tools to work with their data?
Thanks for any insight...
Jeff Godfrey
