Re: [sqlite] LEFT INNER JOIN a second database
>And every reply, even if it is not helpful, is worthy of grateful >thanks because it is being provided free by a list member. This is why i put help in quotes. Please tell me how helpfull comments like: "Rule 1: Assume nothing. Demand proof. " "So produce believable evidence. " "Nobody is going to run your exe in a pink fit." When I offer help to people in forums, I try not to barb it with judgmental invalidation of what the person is doing, has done or is trying to do. Your help has been great. Precise suggestions. I thank you for that. perhaps others could learn from your example... Asking question here can often feel like venturing into the Alamo. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LEFT INNER JOIN a second database
yes the example works as expected. This is the first step in debugging this problem which could of course turn out to be my code. But since you were baying for something to execute... I should also say that it is interesting to note the "vigor" with which some of you are 'helping'! The next step is use the Windows application (binary, executable fill in you choice semantic here) on the 'secret' databases that contain sightly different (top secret) fields... Hopefully get to that later monday. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LEFT INNER JOIN a second database
To make it really easy, I have created three .sql files and an application that is NOT command line akward. There are three .sql files with the statements needed to create two databases and execute the outer join. Drag and drop them onto the application to execute them... http://www.transferbigfiles.com/Get.aspx?id=ebd730fd-17ad-45c9-a341-43d078b118e3 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LEFT INNER JOIN a second database
Hello, There is no need to get upset. I am trying to simplify the table names and column names to make your life easier... You can assume that I have carefully studied the responses I got and tried everything suggested. I have also read all the links given. Thank you for those I then implemented the suggestion: well, for one, do you have an "ATTACH 'DatabseTwo.sdb' AS db2" first? If yes, you could do SELECT.. FROM TableOne d LEFT OUTER JOIN db2.TableTwo n ON n.MyID=d.MyID WHERE d.Zip > 8 ORDER BY d.Zip This is not working for me. TableTwo is not found. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LEFT INNER JOIN a second database
I have read and searched but I am not able to get the following statement to run: SELECT MyID, Zip FROM TableOne d LEFT OUTER JOIN DatabseTwo.sdb.TableTwo n ON n.MyID=d.MyID WHERE d.Zip > 8 ORDER BY d.Zip I just get error at "." I tried specifiying the databse name without the file extension by no joy. What am I missing here? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Progress update while Prepare() is executing
Thnk you for your thoughts. I have learned a few things as always.
There is a tenency these days to question the question these days
as most developers could benefit from a better design. It tends
to divert the question in directions that were not intended
and requires defending the underlying structure that gave rise
to the question.
In this case a GREAT deal of effort went into this particular schema
and the tip of the iceberg is the SQLite portion. Many things
have been dictated by the submerged layers and layers of server
database structure.
I have given a fair amount of detail about the table structure, its basically
a few BIG INTs and some Blobs. I gave this detail so that you can see that
ten seconds for the Prepare() is significant compared to the
size and structure of the database. This is not a criticism
of SQLite. With the proper keys this step would be instant.
But here is a real world case where progress indication would
help.
>Do you also COMMIT when done?
Oh yes
>If the replace statement is the same every time, you only
have to prepare it once.
Yes we use the reset()
>If RecID is a uniuqe integer anyway...
yes
>RecID becomes an alias for ROWID, and you get the index for
free. This will speed up your SELECT above considerably.
yup
>Make sure to define the Blobs as the last columns,
done that
in order of increasing expected size.
hmmm they are all around the same size but one is slightly larger.
I will have to play with that thanks
>it is better to store the blobs in separate tables,
no can do
>Blobs performance is much better with large pages.
forget to check that one thx
>You can see what it does with EXPLAIN SELECT
Yes but that doesn't really get me where I want to go
>Do you mean the REPLACE or the SELECT ?
the first Querie after the UPSERT phase is a SELECT which takes a while
>The progress indicator of both REPLACE and SELECT can be
driven by the while { step() } loops in your program.
As explained that zips along no prob. I need progress for
Prepare() phase
A progress callback is not of much use here, except for the
first step(), because the first step() returns after any
intermediate tables have been built.
>To get a % progress indicator you need a more or less
>accurate estimate of what 100% is...
>Exact progress indicators are hard.
Hence the post
> It's better to optimize the schema
Well I really just need a progress indicator
but I am allways interested to hear ways to increase performance
>With proper design, 40k rows and no joins, most selects
should be really fast.
With all the relevant indexs, you are right, but this happens to be one
case where that is not possible as I explained, there exists a general
case that would benefit from some kind of progress indication.
Now it doesnt have to be a smooth progress bar, it could just be a notification
from the completion of each step in the process, perhaps a code anything
that could be reported.
Users hav NO PATIENCE. They want to see SOMETHING... anything real will do.
They hate cute animations that are eye candy disconeected from
the underlying process... at least thats what I am told by people
that make a LOT more money than I do at this stuff...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Progress update while Prepare() is executing
Yes this is the first SELECT Querie that returns a result set. - The Client app launches and opens a connection, - A TRANSACTION is begun - Multiple UPSERTS are performed if data is available ( about 5k rows of: REPLACE INTO MyTable ( RowID, UpDtUNIX, Zip, cCode, RecID, jDate, Blob1, Blob2, Blob3) VALUES ( ?,?,?,?,?,?,?,?,? ) prepare() bind() step() reset() finalize() - A querie is executed to retrieve a result set from the table of 40k records: SELECT RowID, Zip, RecID, Blob1, Blob2 FROM MyTable WHERE RecID=1 OR RecID=4 OR RecID=5 AND jDate > MinJulianDate AND jDate < MaxJulianDat ORDER BY cCode RowID is the Primary Key and there is an Index for jDate but not RecID or cCode. Now I suspect your (very valid) first suggestion will be to add an index for these columns, then the first querie will not have to do all the processing which I assume is something like reading the entire database and making a temporary index for the other columns that has a shelf life of the connection hande?) Unfortunatly the design spec is VERy focused upon the UPSERT phase of the app. This has to meet some requirements that have intially dictated the reduction in the number of keys. While it would make sense to discuss ways to possibly improve that, the real point is that no one cares that the first Querie takes so long as long as there some progress indication (and I dont mean a endlessly repeating progress bar) So I am wondering if there is a way for SQLite to report progress? The other scenario where this would make sense would be when there are many columns and a user is given the ability to design his own Querie that may include columns that are not indexed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Progress update while Prepare() is executing
I am using sqlite3_prepare_v2() to process my SQl queries. This takes around 80% of the processing time compared to recovering the result set. Once the querire has been processed i can obviously provide a progress bar when stepping through the result set, but what progress indication is available during the prepare stage? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Progress update while Prepare() is executing
I am using sqlite3_prepare_v2() to process my SQl queries. This takes around 80% of the processing time compared to recovering the result set. Once the querire has been processed i can obviously provide a progress bar when stepping through the result set, but what progress indication is available during the prepare stage? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
Thanks for the link. Unfortunatly its a little expensive and probably 50% slower than my implementation. No offense, but C is a language that a lot of us tolerate and is not the panacea that some C developers like to believe it is. Reading C is like reading Chinese. ASM may not be a high level language, but it certainly is fast. My knowledge of encryption is above adequate, while I will be the first to admit my understanding of the inner workings of a database is not. As I grapple to understand how Encryption may be applied to a database project I am developing, I have displayed my ignorance in the hope of learning. It is fairly obvious that some very talented developers on this forum would prefer to use this as an exercise in pedantic sniping rather than offer substantive help. To the others, I thank you. Cory Nelson <[EMAIL PROTECTED]> wrote: On Mon, Aug 25, 2008 at 6:33 PM, Derek Developer wrote: > Dennis thank you for taking the time to explain that. I have read the > Architecture page and I think I have a better idea. > > Since this does seem to be a viable way to protect the data I would like to > implement the schema, but using AES instead of MD5 which is unsecure. > > Has anyone done this and posted the code? > drh sells a version with encryption builtin here: http://www.hwaci.com/sw/sqlite/prosupport.html I don't mean any offense here, but in case you aren't doing this to learn and will really be storing people's credit cards and socials: you are not knowledgeable enough in this area to be writing any production encryption code. Doing so would be a disservice to any customers. Definitely use available tested code in this case, like drh's version. -- Cory Nelson ___ 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] equality searches and range searches with encrypteddata
>No, the index is stored in a separate Btree. The master table simply >stores the page number of that btree's root page. With that information >SQLite can read and decrypt the index's root page and begin a O(log N) >search for the first matching record, reading in and decrypting more >pages as required. >... an index will work securely and efficiently for such a search in an >encrypted database. Dennis thank you for taking the time to explain that. I have read the Architecture page and I think I have a better idea. Since this does seem to be a viable way to protect the data I would like to implement the schema, but using AES instead of MD5 which is unsecure. Has anyone done this and posted the code? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
appologies, "master database" should read "MASTER TABLE" This is where the index is stored I assume? You suggested that each record would NOT be decrypted for a SELECT because the INDEX would handle that work. Since the INDEX is proabably not a MAC hash of the SS# then it would be accessible from the MASTER TABLE yes? If that is the case then clearly an Index is not a viable solution and each page will have to be decrypted to perform and equlity/range search. That doesn't sound like it will be fast... >Unlike the situation here, you can't encrypt individual columns I have several ASm implementations of AES and secure HASH Algos that are very very fast. I can apply these to individual columns for each row. I suspect that, as has been pointed out, the disk access would be more time consuming than the encryption. I would then do a MAC index in a secondary column. Before I implement this, I wanted to make sure I understand the implementation at the page level. Obviously a single Row can take up more than one page, but I am still not clear if a single page can ever contain more than one Row? Igor Tandetnik <[EMAIL PROTECTED]> wrote: Derek Developer wrote: > So if the data is indexed prior to the encryption step, does SQLite > manage that index internally as a Btree that s stored somewhere? Is > it part of the MASTER database? I'm not sure what you mean by "MASTER database" (as opposed to which other database?) SQLite stores everything in a single file - data, indexes and all. > Obviously there would be a security issue if the index is accessible > as per this: > http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx Unlike the situation here, you can't encrypt individual columns or tables with SQLite (at least not using any products I'm familiar with). You encrypt the whole file. > If I am storing CC# or SS#, the index would contain them yes? Well, if you created an index on those columns, then of course the index would contain values from them. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ 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] equality searches and range searches with encrypteddata
Thanks Igor, So if the data is indexed prior to the encryption step, does SQLite manage that index internally as a Btree that s stored somewhere? Is it part of the MASTER database? Obviously there would be a security issue if the index is accessible as per this: http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx If I am storing CC# or SS#, the index would contain them yes? Igor Tandetnik <[EMAIL PROTECTED]> wrote: Derek Developer wrote: > Thank you for your replys, but I am still not sure I understand how a > Query is executed on (page) encrypted data wihout either indexing the > data prior to encrption, creating a secondary hash column of the data > or simply decrypting every page to get at the underlying data? The data _is_ indexed prior to encryption (assuming you did create an index on the appropriate column, of course), then the pages containing the index are themselves encrypted when written to the file (and decrypted when read back, of course). Encryption is built into the I/O. Whenever a page-worth of data is read from disk, it is decrypted right afterwards. When a page is written, it's encrypted right before. The I/O engine doesn't care what kind of data is on that page - it's only concerned about shuffling bytes in and out of storage. The database engine doesn't care whether the file is encrypted or not - it always gets plaintext pages from I/O subsystem. 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] equality searches and range searches with encrypted data
Thank you for your replys, but I am still not sure I understand how a Query is executed on (page) encrypted data wihout either indexing the data prior to encrption, creating a secondary hash column of the data or simply decrypting every page to get at the underlying data? Would greatly appreciate a detailed explanation. Cory Nelson <[EMAIL PROTECTED]> wrote: On Sun, Aug 24, 2008 at 12:36 AM, Derek Developer wrote: > Well that implies that the "parsing" decrypts each page in the database for > each query. How else would it traverse a key that is encypted? > > Isn't that going to be very slow? > Are you thinking it needs to decrypt the entire database for each query? If so - that's not the case. XTS (or some method like it) is used, where each page can be decrypted by itself so you end up with the exact same amount of I/O as a non-encrypted DB. > > Cory Nelson wrote: On Sun, Aug 24, 2008 at 12:07 AM, Derek Developer > wrote: >> I am still not clear if page level encryption permits equality searches and >> range searches? >> Intuitively it would seem that these searches would require every page to be >> decrypted to access the column data for each record... >> > > no functionality is lost. pages already need to be parsed - > encryption can just be thought of as another phase of this parsing. > -- Cory Nelson ___ 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] equality searches and range searches with encrypted data
Well that implies that the "parsing" decrypts each page in the database for each query. How else would it traverse a key that is encypted? Isn't that going to be very slow? Cory Nelson <[EMAIL PROTECTED]> wrote: On Sun, Aug 24, 2008 at 12:07 AM, Derek Developer wrote: > I am still not clear if page level encryption permits equality searches and > range searches? > Intuitively it would seem that these searches would require every page to be > decrypted to access the column data for each record... > no functionality is lost. pages already need to be parsed - encryption can just be thought of as another phase of this parsing. -- Cory Nelson ___ 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] equality searches and range searches with encrypted data
I am still not clear if page level encryption permits equality searches and range searches? Intuitively it would seem that these searches would require every page to be decrypted to access the column data for each record... unless a proxy Index is used as suggested here http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx in the form of a MAC. Does SQLite maintain such a proxy for Keys when Page encryption is enabled? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite remote management tools?
We are in Beta with a windows tool: The RemSQL Library consists of standard dynamic link libraries (DLLs) that can be used in a wide variety of programming languages. Because most programming languages support calling functions exported from a DLL, the Library Edition provides the broadest compatibility with the various development tools available for the Windows platform. The Library Edition is ideal for languages such as C, Delphi, VB, FreeBasic, PowerBASIC etc . The RemSQL Library uses standard Windows DLLs just like SQLite.DLL (not COM activeX) which can be used by virtually any Windows programming language or scripting tool. RemSQL is a high-performance, self-contained, zero-configuration, transactional library for extending SQLite to a remote server via the internet. It is built around a fast, memory efficient wrapper for the SQLite library, capable of handling large amounts of INSERT/QUERY data/from a remote (or local) SQLite database. Like SQLite, RemSQL is a small (180k) standardized interface Dll that natively provides CGI functionality, Encryption, Hashing, Very secure HTTP/TCP communication, SQLite User Defined Functions, and Supplemental operations like password verification, client software feature authorization, File transfer, remote program launching, Gathering Server metrics and any other remote user operation requiring an efficient communication framework. RemSQL is an IPC (interprocess communication) utilizing HTTP via TCP (Transmission Control Protocol) for the Windows platform. Both Client and Server must be running windows and utlize the same Dll. On the server, a simple CGI script (CGI.exe) can used to communicate between HTTP and the Dll. This allows low cost commercial windows hosting solutions to be used as a server. Alternatively an ISAPI module can also be created. In keeping with the SQLite concept, RemSQL does not need to be "installed" before it is used. There is no "setup" procedure. There is no Service that needs to be started, stopped, or configured, no Firewall "issues" to deal with and no Router port forwarding to be done. There is no need for an administrator to create accounts or assign access permissions to users, and no incompatibility issues with each new release of the database. There are no configuration files or setup utilities or installers that "might take a minute or two". Nothing needs to be done to tell the system that RemSQL is running. There is no MS-Management-Console, DCOM Config or Proxy/Interface-Creation/Registration. The user will not have to deal with a constant barrage of error messages, warnings, and popups: DNS errors, transient network outages, ASP errors, Javascript problems, missing plugins, temporary server outages, incorrect or expired certificates, problems connecting to the MySQL backend (common on any slashdotted web site), and a whole host of other issues. You will not be needing 63MB of compressed runtime modules or any external libraries. Pre-releases of the WinFX Runtime Components 3.0, the WinFX SDK, the Platform SDK, the Windows SDK, the .NET Framework redistributable, Microsoft Visual Studio, and their dependencies will not interfere with or cause anything to "fail or break functionality". There is no Base64 encoding and the associated XML bloating to 300% or more (some WMV files) Just copy RemSQL3.Dll to the target machine, and RemSQL3CGI.exe to the Server, and begin work. RemSQL is released under the Apache 2.0 license and is therefore free for commercial use and distribution. If that is what you are looking for and would like to beta test this and/or convert the headers for your language, please contact me. Derek Richard Klein <[EMAIL PROTECTED]> wrote: Federico Granata wrote: >> I was hoping there might be a client/server management tool out there. >> I would need the source code, since the server part would need to be >> ported to my embedded device. >> > Maybe you haven't yet read this http://www.sqlite.org/serverless.html > There isn't a sqlite server so you can't have a sqlite client, local or > remote. Sqlite doesn't come with a server, but some enterprising tool developer could write one, right? :-) - Richard ___ 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] Multiple Row Updates
Just wanted to check that there is no "reset" statement that I can use with UPDATE's. My understanding is that for multiple Row updates the complete sequence needs to be executed for each row ie: "UPDATE mytable SET ' name=?, street=?, Phone=? WHERE ROWID=333" Prepare Bind the data step finalize then the next row must complete the same sequence ie: "UPDATE mytable SET ' name=?, street=?, Phone=? WHERE ROWID=333" Prepare Bind the data step finalize Obviously I can do all these within a BEGIN/END TRANSACTION but was just wondering if there is any way to use the reset statement and update the ROWID? This would save a lot of time for large numbers of rows. Testing with Seinfeld data from Michael Owens excellent book: http://books.google.com/books?id=VsZ5bUh0XAkC=PA75=PA75=sqlite+seinfeld=web=u42Lep_3F7=A3whrQ0XJbW7DBQbPhyspKdHJuc=en Download: http://www.apress.com/book/downloadfile/2847 (also would love to figure out how to respond to my own threads. I cannot seem to find the link in the digests I am getting at my email account) - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] endless loop example
In trying to break my code with the seinfeld database examples, I found this. SELECT f.name as food, e1.name, e1.season, e2.name, e2.season FROM episodes e1, foods_episodes fe1, foods f, episodes e2, foods_episodes fe2 Why does this put SQLite into an endless loop? (I am not using the shell tool, just preparing the statement as is and stepping throug the rows) - Looking for last minute shopping deals? Find them fast with Yahoo! Search. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sorting NULs with Dynamic Typing
In SQLite a NUL is nothing. Inserting a NUL into a column defined as Integer for example creates a zero length entry not an Integer of value zero. How do traditional databases with static typing deal with this? Do they check the column delaration and convert the NUL to a zero value upon entry? How does SQLite handle sorting with a column that contains values (including zero) and NULs? Are the NULs converted to zero for the purposes of sorting? - Never miss a thing. Make Yahoo your homepage. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Complex Query
(2nd attempt... bounced back for some reason...) In testing a my code I came across this example. Could someone help me understand what this syntax is doing please (from the Seinfeld demo database examples) ...m col ...h on ...w 20 17 6 23 6 ...e on Is this some form of typecasting? This is how they are used SELECT f.name as food, e1.name, e1.season, e2.name, e2.season FROM episodes e1, foods_episodes fe1, foods f, episodes e2, foods_episodes fe2 WHERE -- Get foods in season 4 (e1.id = fe1.episode_id AND e1.season = 4) AND fe1.food_id = f.id -- Link foods with all other epsisodes AND (fe1.food_id = fe2.food_id) -- Link with their respective episodes and filter out e1's season AND (fe2.episode_id = e2.id AND e2.season != e1.season) ORDER BY f.name; SELECT e.name AS Episode, COUNT(f.id) AS Foods FROM foods f JOIN foods_episodes fe on f.id=fe.food_id JOIN episodes e on fe.episode_id=e.id GROUP BY e.id ORDER BY Foods DESC LIMIT 10; SELECT 1 IN (1,2,3); SELECT 2 IN (3,4,5); SELECT COUNT(*) FROM foods WHERE type_id IN (1,2); SELECT COUNT(*) FROM foods WHERE type_id IN (SELECT id FROM food_types WHERE name='Bakery' OR name='Cereal'); SELECT name, (SELECT COUNT(id) FROM foods_episodes WHERE food_id=f.id) count FROM foods f ORDER BY count DESC LIMIT 10; - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Complex SELECT Syntax
In testing a my code I came across this example. Could someone help me understand what this syntax is doing please (from the Seinfeld demo database examples) ..m col ..h on ..w 20 17 6 23 6 ..e on Is this some form of typecasting? This is how they are used SELECT f.name as food, e1.name, e1.season, e2.name, e2.season FROM episodes e1, foods_episodes fe1, foods f, episodes e2, foods_episodes fe2 WHERE -- Get foods in season 4 (e1.id = fe1.episode_id AND e1.season = 4) AND fe1.food_id = f.id -- Link foods with all other epsisodes AND (fe1.food_id = fe2.food_id) -- Link with their respective episodes and filter out e1's season AND (fe2.episode_id = e2.id AND e2.season != e1.season) ORDER BY f.name; SELECT e.name AS Episode, COUNT(f.id) AS Foods FROM foods f JOIN foods_episodes fe on f.id=fe.food_id JOIN episodes e on fe.episode_id=e.id GROUP BY e.id ORDER BY Foods DESC LIMIT 10; SELECT 1 IN (1,2,3); SELECT 2 IN (3,4,5); SELECT COUNT(*) FROM foods WHERE type_id IN (1,2); SELECT COUNT(*) FROM foods WHERE type_id IN (SELECT id FROM food_types WHERE name='Bakery' OR name='Cereal'); SELECT name, (SELECT COUNT(id) FROM foods_episodes WHERE food_id=f.id) count FROM foods f ORDER BY count DESC LIMIT 10; - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using WHERE within a BLOB
Is it possible to search a sequence of bytes within a BLOB? Lets say i have a BLOB that contains 4 text fields 32 characters long ie firstname, lastname, street, city. Can I form a query that looks something like: SELECT Bytes33to64 FROM MyTable WHERE Bytes33to64 LIKE 'Jones' instead of SELECT lastname FROM MyTable WHERE lastname LIKE 'Jones' Is there anyway to do this? This would allow me to store a single UDT as a BLOB and treat it like a series of columns in a table. - Looking for last minute shopping deals? Find them fast with Yahoo! Search. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite as persitent Object/UDT storage
I watched the google? video presentation DRH gave (very nice, thank you). I was interested to hear him suggest using SQLite Tables as persistent storage for Objects/UDTs. I want to do this for an application that has a very well defined UDT that is used throughout the application. It would make sense to simply store the UDT array locally using SQLite. The part I am not clear about is how to integrate this with the main database on the server. This database is a larger superset of the local database and requires all the elements of the UDT be broken out into a seperate table, one per column, so that a query can be made against the individual elements. The return would be the rows of the UDT table NOT all the individual elements. Lets say the main server database contains things like, name, address, contact info, personal metrics (like height and weight) etc etc for a total of say 60 fields. The client will issue a query for all the Rows of people over a certain height, under a certain weight and living in one of 13 specified zip codes. Easy, but instead of returning all 66 fields I want to return the UDT for the records. This implies the second UDT table that has two columns?, a foreign key and a UDT Blob for each Row of the primary table. To take it a step further, the UDT would be compressed to minimize xfer time and server memory overhead. I do not have a lot of experience with SQL but it seems a query would contain a JOIN to include the UDT Table? Does this design have any unforseen problems thus far? The next part of the problem is that new data Rows are updated with say 10 additional fields. For example the address is processed to find the Zip+5 code (an existing field created by the initial create string) Calculations are performed to determine other field values. After each one of these steps the corresponding UDT table must be updated. This would require pulling every fragment of data out of the Row in question from the primary table, populating the UDT and then upddating the corresponding Row in the secondary UDT table. This is not a problem so much as an implied step with this method of using a Table for the UDT storage. (comments?) Finally there is the choice of the Primary Key. In my (inexperienced) thinking, I would like to use something other than the RecID (INTEGER PRIMARY KEY) as this is more database dependant than data dependant. There is a field in the data that is unique, it is a combination of numbers and letters and dashes, but it is missing in one in a thousand records unfortunatly. I could create a fake one, but then I run the risk of a duplicate later. So I was thinking of using Date (Julian format) to generate a base number (all the records have some date attached to them) and then use something else (???) added to that. That would provide a convenient INTEGER Primary key I could Hash the record and use that. This would provide a unique 32byte binary hash. I would choose the fields to make the hash carefully, but if two records have empty fields for all that I chose, I no longer have a unique Key. I could use a random number generator and simply check to see if the new random number has been assigned before... but I might as well use the RecID. Now I suspect you may be thinking "just use the RecID" because it is guaranteed unique. I could. But the first batch of data will be chronologically after the final batch of data. So all my RecID's will be counter intuitive. (not good at 2am when some disaster has to be handled) Also, if I transfere the database to another system down the road, that database will be creating its own RecID unless I force it to use the existing RecIDs. Its just messy. I would rather create a Data driven Primary key, that I also use in the application, that is independant for the RecID used by the Database. I would appreciate any thoughts on this from this group. I am sure none of these issues are new to you guys! - Never miss a thing. Make Yahoo your homepage. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
