[sqlite] Query Optimization
SELECT customer_id FROM customers WHERE cust_balance != (select coalesce(sum(balance_due), 0) FROM invoice WHERE status='Active' AND invoice.customer_id = customers.customer_id) The above query is used to determine if any stored balances are out of date. It works very well but is *really* slow when the customer and invoice tables get into the thousands of rows. Is there a better way to accomplish the same thing, or some combination of indexes I can create to help speed that query up? Currently indexes are on the customer_id columns of both tables as well as the cust_balance field in customers. Thanks! -- - Mitchell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Join Syntax Questions
I could swear I've done this type of thing before and am sure I'm overlooking something simple. Is this correct syntax? SELECT im.invoice_date as invoice_date,im.pay_by as due_date,im.invoice_id as invoice_id, im.invoice_number as invoice_number,im.invoice_date as created,im.status as status, im.next_invoice as next_invoice, im.tax as tax,im.tax2 as tax2,im.subtotal as subtotal,im.total as total,im.balance_due as balance_due, im.customer_number as customer_number, im.customer_name as customer_name FROM invoice_master as im LEFT JOIN ( SELECT coalesce(sum(payment_applied), 0.00) as total_paid,invoice_id as theiid FROM payments WHERE void='f' AND created <= 1204243199) the_payments on im.invoice_id = the_payments.theiid WHERE im.invoice_date between 1201478400 And 1204243199 AND im.status != 'Forwarded' GROUP BY im.invoice_id ORDER BY im.balance_due DESC,im.invoice_date,im.total DESC,im.customer_name With or without the join I get the exact same result set. I don't even see null results for the columns that are supposed to be pulled in from the join. I have a habit of mixing SQLite and PostgreSQL syntax, have I done it again? -- - Mitchell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange .dump output
Sure enough, that's in the sqlite_master table. I guess I did do it back when I created the database but I can't imagine why.. Thanks! On Dec 11, 2007 2:19 PM, <[EMAIL PROTECTED]> wrote: > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > I've noticed something strange in some of the SQL statements created > > from the command line utility in Windows (the most current version as > > of this morning). > > > > For nearly all of the tables it works well but it puts a ' character > > around the name of one table named invoice_master, which is breaking > > the import into another database. That is the only table name that is > > surrounded by the single quote and I can't for the life of me figure > > out why. Other table names have underscores, so what would be the > > reason of quoting that table name in the CREATE statement with single > > quotes? > > > > The original SQL text used to create the table is stored in the > sqlite_master table. The .dump command should be using this > exact text. > > Are you sure you didn't quote the table name when you originally > created the invoice_master table? > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Strange .dump output
I've noticed something strange in some of the SQL statements created from the command line utility in Windows (the most current version as of this morning). For nearly all of the tables it works well but it puts a ' character around the name of one table named invoice_master, which is breaking the import into another database. That is the only table name that is surrounded by the single quote and I can't for the life of me figure out why. Other table names have underscores, so what would be the reason of quoting that table name in the CREATE statement with single quotes? -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
I found a solution - apparently the DB file was just hosed. There were actually duplicates stored somehow. I dumped the database to a flat file and modified the IDs by hand then imported the data into a new SQLite database. That seemed to do the trick. I'm not sure how the corruption happened but my users have been known to do things like store the live data file on a USB flash drive and use it so it's hard to tell! Thanks to all for the information and suggestions! On Nov 20, 2007 7:43 PM, Trey Mack <[EMAIL PROTECTED]> wrote: > >>>> INSERT INTO invoice_items > >>>> (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > >>>> VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > >>>> DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > Does this work? > > INSERT INTO invoice_items > (invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > VALUES (899,1001975,'HD0001 - ASH - YL','','HOUSE > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
No triggers at all in the database. It's pretty vanilla.. On Nov 20, 2007 6:04 PM, <[EMAIL PROTECTED]> wrote: > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > > Here we go. I am accessing the database through an ODBC connection > > using the most recent version from Christian's site.. > > > > The table schema : > > > > CREATE TABLE invoice_items ( > > item_id INTEGER PRIMARY KEY, > > invoice_id int4, > > product_id int4, > > product_name text , > > sku text , > > description text , > > quantity text, > > price int4, > > cost int4, > > taxable bool, > > format_price text, > > format_total_price text, > > taxable2 boolean) > > > > The query : > > > > INSERT INTO invoice_items > > (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > > VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > > > The error : > > > > PRIMARY KEY must be unique (19) > > > > I wonder if this error might be coming from a TRIGGER > and not from the INSERT statement itself? > > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
select max(item_id) from invoice_items; Produces "803" - but that is pretty clearly wrong when I look at the dataset a count() returns 15503 records in that table. min(item_id) is 1 (as expected). I can insert records if I specify a unique item_id. On Nov 20, 2007 5:50 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > > Mitchell Vincent wrote: > > Here we go. I am accessing the database through an ODBC connection > > using the most recent version from Christian's site.. > > > > The table schema : > > > > CREATE TABLE invoice_items ( > > item_id INTEGER PRIMARY KEY, > > invoice_id int4, > > product_id int4, > > product_name text , > > sku text , > > description text , > > quantity text, > > price int4, > > cost int4, > > taxable bool, > > format_price text, > > format_total_price text, > > taxable2 boolean) > > > > The query : > > > > INSERT INTO invoice_items > > (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > > VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > > > The error : > > > > PRIMARY KEY must be unique (19) > > > > I can't post the database itself because it contains some sensitive > > information (as you might have guessed, it's a database of billing > > information). > > > > The INSERT query for the invoice_items table has "NULL" hard coded in > > for item_id, so I know it's not accidentally being set to an existing > > value. > > > > > > > Can you select the max and min item_id values form the database and > report them back here. > > Can you then change your code to insert a new dummy record with an > item_id that is set to one greater than the maximum value, rather than > your hard coded NULL value. I just want to see if you can insert a > properly formed record into your database at all. You should then delete > that record so it doesn't cause a problem in your database later. > > Dennis Cote > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
Here we go. I am accessing the database through an ODBC connection using the most recent version from Christian's site.. The table schema : CREATE TABLE invoice_items ( item_id INTEGER PRIMARY KEY, invoice_id int4, product_id int4, product_name text , sku text , description text , quantity text, price int4, cost int4, taxable bool, format_price text, format_total_price text, taxable2 boolean) The query : INSERT INTO invoice_items (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE DIVIDED',1,800,450,'f','f','$8.00','$8.00') The error : PRIMARY KEY must be unique (19) I can't post the database itself because it contains some sensitive information (as you might have guessed, it's a database of billing information). The INSERT query for the invoice_items table has "NULL" hard coded in for item_id, so I know it's not accidentally being set to an existing value. On Nov 20, 2007 4:44 PM, <[EMAIL PROTECTED]> wrote: > > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > I have a primary key that auto increments and has apparently > > overlapped back on to itself. > > > > INSERT into mytable(id,name) values(NULL,'test'); > > > > ... is giving me "primary key must be unique" errors. > > > > How can I reset the sequence for a primary key? The table only has > > about 15000 records in it and I've never seen this happen before.. > > > > There are over 9e18 keys. So if you create 1 billion new > rows per second, continuously, it will take you 262 years to > exhaust all primary keys. Since SQLite has only been in > existance for 7 years, this is clearly impossible so you > must be doing something wrong. > > Perhaps if you provided more details we could help you. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
No doubt, it is obviously something that has been screwed up. Unfortunately I'm in "hot fix" mode right now and the investigation into why it happened will happen after I get this customer back up and running. I have a feeling it has something to do with the ODBC driver being used. I'll gather and post the schema of the table along with the query. The "null" value is hard-coded into the query string of the INSERT query so it is definitely not being set incorrectly by the software - I let SQLite handle the ID assignment. Is it possible to set the sequence that a primary key uses to track the next ID? If so, how can I do it? Thanks very much for the reply! On Nov 20, 2007 4:44 PM, <[EMAIL PROTECTED]> wrote: > > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > I have a primary key that auto increments and has apparently > > overlapped back on to itself. > > > > INSERT into mytable(id,name) values(NULL,'test'); > > > > ... is giving me "primary key must be unique" errors. > > > > How can I reset the sequence for a primary key? The table only has > > about 15000 records in it and I've never seen this happen before.. > > > > There are over 9e18 keys. So if you create 1 billion new > rows per second, continuously, it will take you 262 years to > exhaust all primary keys. Since SQLite has only been in > existance for 7 years, this is clearly impossible so you > must be doing something wrong. > > Perhaps if you provided more details we could help you. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ltrime() and rtrim() not understood with ODBC?
Ahh, that's it. On the ODBC driver information page I see "So far it has been tested with SQLite 2.8.17 and SQLite 3.3.13" Darn! Thanks Joe! On 8/3/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > rtrim was introduced on 17-Mar-07, and was in the sqlite 3.3.14 release. > > http://www.sqlite.org/cvstrac/chngview?cn=3698 > > What sqlite version is your ODBC driver? > > select sqlite_version(); > > --- Mitchell Vincent <[EMAIL PROTECTED]> wrote: > > If I use sqlite3.exe to execute the query "Select > > customer_id,customer_number FROM customers where customer_number != > > rtrim(customer_number);" it works great but as soon as I run that > > query through using the SQLite ODBC driver (using the latest > > available) I get "no such function: rtrim (1)" > > > > I thought at first that maybe sqlite3.exe just had extra extension > > functions compiled in but http://www.sqlite.org/lang_expr.html says > > ltrim and rtrim are "core". > > > > Is there something I have to do to get ltrim/rtrim to work over ODBC? > > > > > > Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for > today's economy) at Yahoo! Games. > http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow > > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ltrime() and rtrim() not understood with ODBC?
If I use sqlite3.exe to execute the query "Select customer_id,customer_number FROM customers where customer_number != rtrim(customer_number);" it works great but as soon as I run that query through using the SQLite ODBC driver (using the latest available) I get "no such function: rtrim (1)" I thought at first that maybe sqlite3.exe just had extra extension functions compiled in but http://www.sqlite.org/lang_expr.html says ltrim and rtrim are "core". Is there something I have to do to get ltrim/rtrim to work over ODBC? Thanks! -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "database is locked" SQLITE_BUSY when db is on network drive...
Can you do any other write queries (INSERT or UPDATE for example)? On 8/2/07, Chase <[EMAIL PROTECTED]> wrote: > > running sqlite 3.4.1 on mac os x 10.4 > > i've set up a file share on another mac running 10.4 and placed a small > db file on the share and chmod'ed it to 777 (full access). > > i go to the development mac and log into the other mac as the user who > owns that db file. > > the path on my dev mac to that shared db is now: > /Volumes/SharedFolder/smalldb.db > > the test app (which works perfectly with the same exact small db > sitting on its local drive) is launched and a connection is made to the > db sitting on the other mac. > > it connects fine. no errors. but then i try to create a temp table > (which, like i said, works if the db is local) it fails immediately > with SQLITE_BUSY "database is locked". > > NO ONE else is accessing this database file, so how is that possible? > > > what am i doing wrong? > > - chase > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Query Indexes
Is there any way to determine if a query is using an index or not? In
PostgreSQL the "explain" works to tell whether a table is being
sequentially scanned or not..
I have a query :
SELECT *,(total - balance_due) as total_paid FROM invoice_master WHERE
lower(invoice_number) LIKE lower('%%') AND status != 'Void' AND
status != 'Recur' AND status != 'Paid' AND status != 'Forwarded'
ORDER BY created ASC LIMIT 25
The lower('%%') gets used with whatever field the user is searching on.
I have indexes on created, status and invoice_number - but apparently
I can't make an index on lower(invoice_number) -- can I?
--
- Mitchell Vincent
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Changing Database Encoding
Wow, it looks like I really did a number on these! I found iconv for Windows and have integrated it into the conversion process... NOw to tackled the Unicode handling (or non-handling) of the SQLite ODBC driver.. This will sure teach me to go around changing things! On 7/30/07, Nuno Lucas <[EMAIL PROTECTED]> wrote: > On 7/30/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: > > I've read a few places that it is not possible to change the encoding > > of a database once it's created. Is it possible to do it in some > > automated way with any of the command line utilities? > > Read about the "pragma encoding" [1] SQL command (you just need to use > it before inserting any data). Note that the "encoding" is always > UNICODE, so you don't gain much with this pragma if you're from the > "western" part of the world > > > I converted a database from SQLite 2.X to 3.X using sqlite.exe's .dump > > function and apparently didn't set the encoding correctly as any > > non-English text isn't accessible. I think I need to set the encoding > > to UTF8 now, though it "just worked" before, so I'm not sure what I > > accidentally did right the first time :-) > > I don't think that is your problem. You were probably using a 2.x > database in "8-bit" mode, not UTF-8. Things can get ugly if you used > the UTF-8 library version on non-UTF-8 strings. > > The only solution I can see is to use something like "iconv" to > translate the dump to UTF-8 before inserting the data into a 3.x > database. That can be more difficult than you think in case of > mismatched use of library versions. > > Regards, > ~Nuno Lucas > > > > > Any help is appreciated! Thanks! > > > > -- > > - Mitchell Vincent > > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Changing Database Encoding
I've read a few places that it is not possible to change the encoding of a database once it's created. Is it possible to do it in some automated way with any of the command line utilities? I converted a database from SQLite 2.X to 3.X using sqlite.exe's .dump function and apparently didn't set the encoding correctly as any non-English text isn't accessible. I think I need to set the encoding to UTF8 now, though it "just worked" before, so I'm not sure what I accidentally did right the first time :-) Any help is appreciated! Thanks! -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DISTINCT and Indexes
Thanks Joe! Just what I was looking for! On 7/7/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > SELECT DISTINCT customers.bill_state as the_result from customers > UNION SELECT DISTINCT invoice_master.bill_state FROM invoice_master > UNION SELECT DISTINCT customers.ship_state from customers UNION SELECT > DISTINCT invoice_master.ship_state from invoice_master; > > Can anyone help speed this query up for large(r) data sets or a > suggest a more efficient way of getting the unique list of states? Strange... I thought DISTINCT would have used indexes if available, but it doesn't appear to be the case: CREATE TABLE customers(bill_state, ship_state); CREATE TABLE invoice_master(bill_state, ship_state); CREATE INDEX customers_bill_state on customers(bill_state); CREATE INDEX customers_ship_state on customers(ship_state); CREATE INDEX invoice_master_bill_state on invoice_master(bill_state); CREATE INDEX invoice_master_ship_state on invoice_master(ship_state); explain query plan select distinct bill_state from customers; 0|0|TABLE customers Since DISTINCT is similar to GROUP BY, you can rewrite you query to pick up the index for each column: explain query plan SELECT bill_state as the_result from customers group by 1 UNION SELECT bill_state FROM invoice_master group by 1 UNION SELECT ship_state from customers group by 1 UNION SELECT ship_state from invoice_master group by 1; 0|0|TABLE customers WITH INDEX customers_bill_state ORDER BY 0|0|TABLE invoice_master WITH INDEX invoice_master_bill_state ORDER BY 0|0|TABLE customers WITH INDEX customers_ship_state ORDER BY 0|0|TABLE invoice_master WITH INDEX invoice_master_ship_state ORDER BY Need a vacation? Get great deals to amazing places on Yahoo! Travel. http://travel.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] ----- -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DISTINCT and Indexes
Hey Andrew! Yes, though that's not what I'm trying to accomplish. The software allows entry of *any* text in the field and I want anything previously entered to appear in the lists after that -- that's why all the trouble. Thanks! On 7/8/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: On 7/7/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: > > I have a query to get all states that a user might have entered, it is : > > SELECT DISTINCT customers.bill_state as the_result from customers > UNION SELECT DISTINCT invoice_master.bill_state FROM invoice_master > UNION SELECT DISTINCT customers.ship_state from customers UNION SELECT > DISTINCT invoice_master.ship_state from invoice_master; > > Can anyone help speed this query up for large(r) data sets or a > suggest a more efficient way of getting the unique list of states? I'd > sure appreciate it! Thanks! > On the other hand, you could merely enter the Fifty States (and provinces, protectorates, commonwealths, etc) into a lookup table, and select from that much faster. :) --andy -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DISTINCT and Indexes
I have a query to get all states that a user might have entered, it is : SELECT DISTINCT customers.bill_state as the_result from customers UNION SELECT DISTINCT invoice_master.bill_state FROM invoice_master UNION SELECT DISTINCT customers.ship_state from customers UNION SELECT DISTINCT invoice_master.ship_state from invoice_master; Can anyone help speed this query up for large(r) data sets or a suggest a more efficient way of getting the unique list of states? I'd sure appreciate it! Thanks! -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] MMap On Solaris
Hi John! Thanks for the reply! I think that makes a good point that the vm page fault is probably faster than the overhead of copying the data to a local buffer. So, page fault or not, I think that's the way I'm going to do it. Again, thanks very much for your input! On 6/12/07, John Stanton <[EMAIL PROTECTED]> wrote: Mitchell Vincent wrote: > Working with some data conversion here (that will eventually go into > an SQLite database). I'm hoping you IO wizards can offer some help on > a question that I've been trying to get answered. > > I'm using Solaris 10 for this. > > If I mmap a large file and use madvise with MADV_SEQUENTIAL and > MADV_WILLNEED, then start processing the file, when will the system > discard pages that have been referenced? I guess what I'm wondering is > if there is any retention of "back" pages? > > Say for example I start reading the file, and after consuming 24,576 > bytes, will the first or second pages still be in memory (assuming > 8192 byte pages)? > > Thanks! > In general it means that the file is mapped into virtual memory. How much of it remains in actual memory depends upon the memory demands on the OS at the time. If the sequential and random advice is used by the OS it is most likely to implement a look ahead for requential access. Not all OS's pay attention to those advisory settings. What you are doing is to access the file as if it were an executing program image. Similar rules apply. The answer is that you cannot assume that pages you have read are in actual memory and you cannot assume that they are not. When you access a page not currently in memory the OS will read it in and find space for it somehow, maybe by discarding some other page. This is an excellent way to read files because you avoid one level of buffer shadowing and get cacheing adjusted to currently available memory. - To unsubscribe, send email to [EMAIL PROTECTED] ----- -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] MMap On Solaris
Working with some data conversion here (that will eventually go into an SQLite database). I'm hoping you IO wizards can offer some help on a question that I've been trying to get answered. I'm using Solaris 10 for this. If I mmap a large file and use madvise with MADV_SEQUENTIAL and MADV_WILLNEED, then start processing the file, when will the system discard pages that have been referenced? I guess what I'm wondering is if there is any retention of "back" pages? Say for example I start reading the file, and after consuming 24,576 bytes, will the first or second pages still be in memory (assuming 8192 byte pages)? Thanks! -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Changing Schema On The Fly
Quite a bit easier said than done! :-) Thanks for the feedback Joe, I appreciate it! On 6/1/07, Joe Wilson <[EMAIL PROTECTED]> wrote: --- Mitchell Vincent <[EMAIL PROTECTED]> wrote: > I guess this isn't possible after all? Get the SQLite ODBC driver source code and alter it to do whatever you like when the type "timestamp" column comes up. > On 5/31/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: > > I have a set of databases that contain a date type called "timestamp". > > I need to make those "integer" so they come through the ODBC driver > > the right way. Is there any way to change all of that through queries > > on-the-fly? I'd like to avoid re-creating all the databases if > > possible.. ___ You snooze, you lose. Get messages ASAP with AutoCheck in the all-new Yahoo! Mail Beta. http://advision.webevents.yahoo.com/mailbeta/newmail_html.html - To unsubscribe, send email to [EMAIL PROTECTED] ----- -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Changing Schema On The Fly
I guess this isn't possible after all? On 5/31/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: I have a set of databases that contain a date type called "timestamp". I need to make those "integer" so they come through the ODBC driver the right way. Is there any way to change all of that through queries on-the-fly? I'd like to avoid re-creating all the databases if possible.. Thanks! -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Changing Schema On The Fly
Thanks Joe! I store "date" values as noon on the given day and toss out the time part to keep things like this from effecting my software. I appreciate the information though! On 5/31/07, Joe Wilson <[EMAIL PROTECTED]> wrote: Be aware of a Windows OS bug that prevents correct conversion of epoch integers to local date/time due to the recent US DST change: http://www.sqlite.org/cvstrac/tktview?tn=2322 Assuming you've applied the Windows OS DST patch, epoch-converted times can be off by an hour for pre-2007 dates in the time periods that used to not be be in daylight savings time, but are now as of 2007. SQLite relies on the underlying OS to handle these timezone/DST issues. In Windows versions prior to Vista, Windows only keeps one DST record per timezone - regardless of different past DST periods. --- Mitchell Vincent <[EMAIL PROTECTED]> wrote: > The issue isn't with SQLite at all, actually, but with the ODBC > driver. I guess the ODBC driver "trusts" SQLite's data type > description because I have a field called "timestamp" that actually > stores an epoch integer in it and ODBC-aware applications see the > datatype as "DateTime" and refuse to pass through the data. So I > really just need to change the type description inside SQLite so it > will report integer to the ODBC driver. And since there are a *lot* of > databases with this problem I'd love to be able to just issue some > sort of update through SQLite to make that happen. Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] ----- -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Changing Schema On The Fly
Yes, I've read that. The issue isn't with SQLite at all, actually, but with the ODBC driver. I guess the ODBC driver "trusts" SQLite's data type description because I have a field called "timestamp" that actually stores an epoch integer in it and ODBC-aware applications see the datatype as "DateTime" and refuse to pass through the data. So I really just need to change the type description inside SQLite so it will report integer to the ODBC driver. And since there are a *lot* of databases with this problem I'd love to be able to just issue some sort of update through SQLite to make that happen. Thanks for the help! On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote: If you read <http://www.sqlite.org/datatype3.html> you will see that, "Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes: -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Changing Schema On The Fly
I have a set of databases that contain a date type called "timestamp". I need to make those "integer" so they come through the ODBC driver the right way. Is there any way to change all of that through queries on-the-fly? I'd like to avoid re-creating all the databases if possible.. Thanks! -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Currency Formatting within SQLite
Hi Igor! The only problem is my lack of understanding on how implementing custom functions in SQLite works. I'm sure your idea is sound I just don't know the details of implementing it. For instance, is this something that has to be compiled into the DLL, or is it database file specific? Now that I know it's possible I'll I'm sure I'll have all my questions answered as I read the documentation. Thanks again! On 4/6/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Mitchell Vincent <[EMAIL PROTECTED]> wrote: > Yes, I know about creating a function but I'm wondering if I can hook > into the already-existing Windows API function for it. Currency > formatting is quite complicated (much more than just storing a > currency "symbol") and Windows has already done the leg work - I just > need to see gain access to the API function for it. I don't understand. You can install a custom function into SQLite engine - the function that you implement. This function can then be used in any SQL statement. Within implementation of that function, you can happily use GetCurrencyFormat or any other API. What again seems to be the problem? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] --------- -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Currency Formatting within SQLite
It sounds so easy when you say it like that! :-) Thanks Mr, Neff! I'll get to reading! On 4/6/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > Yes, I know about creating a function but I'm wondering if I can hook > into the already-existing Windows API function for it. You don't need to write a totally custom function, just something that bridges the two API's. Create a function that implements the expected SQLite call spec and then call out to Windows to get the results. 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mitchell Vincent Sent: Friday, April 06, 2007 11:17 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Currency Formatting within SQLite Yes, I know about creating a function but I'm wondering if I can hook into the already-existing Windows API function for it. Currency formatting is quite complicated (much more than just storing a currency "symbol") and Windows has already done the leg work - I just need to see gain access to the API function for it. The reasons are complicated but suffice it to say that currency formatting is one area that ReportMan (reportman.sourceforge.net) is lacking in. It's really not the job of a database to do this stuff but I'm left with either getting SQLite to do it on the fly or adding another "formatted" text field for each currency field already in the database. Thanks Igor! - To unsubscribe, send email to [EMAIL PROTECTED] ----- -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Currency Formatting within SQLite
I know it's a long shot but is it possible to use the Windows API GetCurrencyFormat() function to format currency strings? I need a layer between my database and report generator to properly format currency (money) strings for various countries. -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQlite 2.8.16 -> SQLite 3
On 3/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > So is a 60%ish reduction in DB size from 2.8.16 to 3.3.13 normal? > 3.3.13 typically generates database files that are 30-40% smaller than 2.8.16. 60% seems excessive, but is not outside the range of possibility. What kind of data are you storing? Floating point numbers are 8 bytes in 3.3.13 versus 17 bytes in 2.8.16. Something like that could account for the difference. Ahh yes, I bet that's it! My 60% was a bit inaccurate. Now that I actually do the math it's closer to 40-55% depending on the types of data (I have do some that store lots of floating point numbers). I was impressed with SQLite before, now I'm REALLY impressed. Thank you!! -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQlite 2.8.16 -> SQLite 3
I'm converting a bunch of databases from SQLite 2.8.16 to SQLite 3.3.13 and am seeing something pretty amazing space saving. So good that it might be too good to be true! For example, a 17 meg database is cut down to 7 megs. That's fantastic if it's just that SQlite is *that* much better at storing the data, but I'm worried I'm missing things. The row counts for all the tables match up but it will take more time for me to write something that compares every field in every data table (there are more than 300 fields total in this database). So is a 60%ish reduction in DB size from 2.8.16 to 3.3.13 normal? Thanks!! -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite 2 to SQLite 3 - Varchar sizes
On 3/9/07, Scott Hess <[EMAIL PROTECTED]> wrote: On 3/9/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: > I'm looking into ways of changing the schema type name when I do my > SQLite2->SQLite3 conversion. Most likely I'll have to pipe the .dump > from the SQLite2 db through a program to replace 'varchar' with > 'text'... I'm working on that now! You could also write something to spelunk through the metadata and alter things to fit. Use 'pragma table_info(table)' to get info about the table, then for each table you want to change, within a transaction create a new table just like the old table with the appropriate changes, use insert-from-select syntax to move the data to the new table, delete the old table, rename the new table to the old name, and commit the transaction. This might not be much fun if your database is very very large, though. Hmmm.. That's not a bad idea. I could do that all in-code. I'm using the ODBC driver to access the SQLite database but I guess that's no big deal. I'd say the biggest database will be in the 30-50 meg range. They're not huge (unless 30 meg is considered huge.. eek..) Thanks!! -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite 2 to SQLite 3 - Varchar sizes
On 3/9/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: If you don't have the word varchar anyplace else, e.g. in your data, you can simply do: sqlite OLD.DB .dump | sed 's/varchar/text/' | sqlite3 NEW.DB If the word varchar may exist elsewhere, or be in various cases (VARCHAR, Varchar, etc.), you'll have to be a bit more creative. "awk" may be your friend. Derrell Fantastic idea, but I'm afraid this will all be on Windows. I'll see about getting a sed-like program to distribute with my converter, though, thanks!! -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite 2 to SQLite 3 - Varchar sizes
On 3/9/07, Christian Werner <[EMAIL PROTECTED]> wrote: Mitchell Vincent wrote: > > I have an old SQLite 2.8 database that shared it's schema with > PostgreSQL. One of the nifty things about PostgreSQL (that admittedly > has me spoiled) is the ability to just say "varchar" without any > length specifier. > > Specifying "varchar" in SQLite works great - no problem at all. Until > I tried to use it with ODBC. The SQLite ODBC driver works fine but > assumes a 255 character limit. As soon as it returns a result longer > than 255 it blows an error. > > I see in the SQLite ODBC driver's documentation that it does support > > 255 varchar fields but I _assume_ have to specify that it's > 255 in > the schema. > > Now comes the fun part. I'm converting these databases (and there are > a LOT of them), I'm doing "sqlite OLD.DB .dump | sqlite3 NEW.DB" which > works flawlessly. Is there any way to change the schema on the fly to > say "Varchar(1024)" instead of just "varchar" (or just use an SQLite > 'type' of "text") ? Mitchell, the artificial limitation of VARCHAR columns to 255 chars in the SQLite ODBC drivers is yet another tribute to M$ACCE$$. If you want a proper untruncated mapping to larger VARCHAR columns use "text" or "memo" in your CREATE TABLE statement. These will be mapped to SQL_LONGVARCHAR by the drivers. Sorry for that inconvenience, but blame billg, not me ;-) Regards, Christian Thanks Christian! I never meant to blame you at all - I figured the assumed 255 char limit was there for good reason! I should have created better schema anyway. I'm looking into ways of changing the schema type name when I do my SQLite2->SQLite3 conversion. Most likely I'll have to pipe the .dump from the SQLite2 db through a program to replace 'varchar' with 'text'... I'm working on that now! -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite 2 to SQLite 3 - Varchar sizes
I have an old SQLite 2.8 database that shared it's schema with PostgreSQL. One of the nifty things about PostgreSQL (that admittedly has me spoiled) is the ability to just say "varchar" without any length specifier. Specifying "varchar" in SQLite works great - no problem at all. Until I tried to use it with ODBC. The SQLite ODBC driver works fine but assumes a 255 character limit. As soon as it returns a result longer than 255 it blows an error. I see in the SQLite ODBC driver's documentation that it does support > 255 varchar fields but I _assume_ have to specify that it's > 255 in the schema. Now comes the fun part. I'm converting these databases (and there are a LOT of them), I'm doing "sqlite OLD.DB .dump | sqlite3 NEW.DB" which works flawlessly. Is there any way to change the schema on the fly to say "Varchar(1024)" instead of just "varchar" (or just use an SQLite 'type' of "text") ? -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Last ID from Primary Key through ODBC
On 3/4/07, Christian Werner <[EMAIL PROTECTED]> wrote: Dan Kennedy wrote: > > On Sat, 2007-03-03 at 19:18 -0500, Mitchell Vincent wrote: > > I found the nifty ODBC driver for SQLite and have been trying to use > > it to replace a 2.8 SQlite implementation in some desktop software. > > It's working very well except for a rather large problem of not being > > able to call the last_id API functions. I need to get the primary keys > > assigned to parent tables in order to preserve referential integrity. > > > > SELECT MAX(id) FROM TABLE is one option but it's pretty dangerous > > seeing as multipl people *could* be accessing this database file at > > the same time.. > > What if you wrap the INSERT and "SELECT MAX(id)" statements together > in a transaction? Or what about issuing a "SELECT last_insert_rowid()" ? As long as it was inside a transaction (and that works), that's the ticket! Thanks! -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Last ID from Primary Key through ODBC
I really don't know. Will it work the way I think it will? :-) On 3/3/07, Dan Kennedy <[EMAIL PROTECTED]> wrote: On Sat, 2007-03-03 at 19:18 -0500, Mitchell Vincent wrote: > I found the nifty ODBC driver for SQLite and have been trying to use > it to replace a 2.8 SQlite implementation in some desktop software. > It's working very well except for a rather large problem of not being > able to call the last_id API functions. I need to get the primary keys > assigned to parent tables in order to preserve referential integrity. > > SELECT MAX(id) FROM TABLE is one option but it's pretty dangerous > seeing as multipl people *could* be accessing this database file at > the same time.. What if you wrap the INSERT and "SELECT MAX(id)" statements together in a transaction? > > Are there alternatives? > > Thanks! > - To unsubscribe, send email to [EMAIL PROTECTED] --------- -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Last ID from Primary Key through ODBC
I found the nifty ODBC driver for SQLite and have been trying to use it to replace a 2.8 SQlite implementation in some desktop software. It's working very well except for a rather large problem of not being able to call the last_id API functions. I need to get the primary keys assigned to parent tables in order to preserve referential integrity. SELECT MAX(id) FROM TABLE is one option but it's pretty dangerous seeing as multipl people *could* be accessing this database file at the same time.. Are there alternatives? Thanks! -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Moving from 2.8.16
I have a business application written that uses SQLite 2.8.16 as the backend database. I love it and it's been as stable as anyone could ever hope for. However, I want to move it forward before it's too late. I'd like to go into it expecting potential problems but from what I've read the move from 2.6.18 to 3.3.13 *should* be pretty smooth. Other than the information here : http://www.sqlite.org/version3.html, is there anything I might run into with queries not working (like any date/time function differences?). I appreciate any tips or tricks anyone would like to share! -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update/Join Howto?
D. Richard Hipp wrote: On Thu, 2005-06-23 at 15:32 -0400, Mitchell Vincent wrote: Shoot! I meant to say that I need it to work with 2.8.16 (which was the entire issue!).. *crosses fingers* any way to make that happen? Not really. Version 2 is in maintenance - meaning bugfixes only. All new features go into version 3. Well, I meant any way to accomplish what I'd like to do in version 2 (doesn't matter how).. -- - Mitchell Vincent - kBilling - Invoices Made Easy! - http://www.k-billing.com
Re: [sqlite] Update/Join Howto?
D. Richard Hipp wrote: On Thu, 2005-06-23 at 14:48 -0400, Mitchell Vincent wrote: UPDATE customer_detail SET customer_id = (SELECT customer_id FROM customers WHERE customers.customer_name = customer_detail.customer_name); This should work. Are you having problems? You'll need version 3.1.0 or later. Shoot! I meant to say that I need it to work with 2.8.16 (which was the entire issue!).. *crosses fingers* any way to make that happen? Thanks! -- - Mitchell Vincent - kBilling - Invoices Made Easy! - http://www.k-billing.com
Re: [sqlite] Vacuum Error with 2.8.16
Mitchell Vincent wrote: D. Richard Hipp wrote: Can you tell me what the original schema is? Replied in private with the schema. I'm also happy to send the custom compiled DLL and the database if you need it. Thanks!!! Any additional thoughts on this? I'm happy to provide any additional information I can! -- - Mitchell Vincent
Re: [sqlite] Vacuum Error with 2.8.16
D. Richard Hipp wrote: Were the blank lines in the original schema, or did they get inserted? Can you reproduce this problem? The blank lines got inserted. I can't reproduce this, no, it is straight from a customer. He insists that he is the only person accessing the database and that it happened after he installed a new version of our software (which updates the database schema and vacuums). It is pretty hard for me to try and reproduce so I was hoping it was related to the vacuum corruption problem and that maybe there was a work around... Eeek. They're going to be ticked! -- - Mitchell Vincent - kBilling - Invoices Made Easy! - http://www.k-billing.com
Re: [sqlite] Vacuum Error with 2.8.16
D. Richard Hipp wrote: On Thu, 2005-03-10 at 16:37 -0500, Mitchell Vincent wrote: sqlite> vacuum; SQL error: near "varchaÃ": syntax error sqlite> These databases had been made with an earlier version of SQLite.. Ideas? Not much to go on I thought maybe it had something to do with the vacuum corruption problems.. I get a "database disk image is malformed" error when doing some queries, but not all. It looks like one of the tables in the db is corrupted, I see this in a .dump : CREATE TABLE payments(payment_id INTEGER PRIMARY KEY, invoice_id int4, customer_id int4, created timestamp, payment_type varchar, payment_amount int4, check_number varchar, cc_name varchaâ; then it continues with another table... I'm hoping this can be repaired or recovered in some way.. Any chance of that? -- - Mitchell Vincent - kBilling - Invoices Made Easy! - http://www.k-billing.com
[sqlite] Vacuum Error with 2.8.16
sqlite> vacuum; SQL error: near "varchaä": syntax error sqlite> These databases had been made with an earlier version of SQLite.. Ideas? -- - Mitchell Vincent - kBilling - Invoices Made Easy! - http://www.k-billing.com
Re: [sqlite] Join query help
Really appreciate your help! However that query doesn't give correct results (though it does give a row for every customer!!!).. The problem is the sum() in the join isn't qualified against the selected customer ID.. Using this : SELECT c.customer_number as customer_number coalesce(ctots.balance_due, 0.00) as balance FROM customers as c left join (Select customer_id cid, sum(balance_due) balance_due FROM invoice_master group by cid) ctots on c.customer_id = ctots.cid ORDER by c.customer_name ASC I need the sum() to be for the current customer ID coming from the other query.. So this : (Select customer_id cid, sum(balance_due) balance_due FROM invoice_master group by cid) Needs to become something like : (Select customer_id cid, sum(balance_due) balance_due FROM invoice_master WHERE customer_id = c.customer_id group by cid) But that doesn't work as I get a "no such column c.customer_id" Kurt Welgehausen wrote: select customers.*, ctots.total from customers, (select customer_id cid, sum(invoice_amount) total from invoice_master group by cid) ctots where customers.customer_id = ctots.cid You're right -- sorry, I wan't paying attention. For 'customers, (subquery) where' substitute 'customers left join (subquery) on', and for 'ctots.total' subsitute 'coalesce(ctots.total, 0.00)'. select customers.*, coalesce(ctots.total, 0.00) from customers left join (select customer_id cid, sum(invoice_amount) total from invoice_master group by cid) ctots on customers.customer_id = ctots.cid Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Join query help
Very nice, however that still only gives me the customer records if they have an invoice in the invoice_master table.. A sub-select or outer join or something of the sort is needed but I can't get it to work.. In PostgreSQL I might do : SELECT *,(SELECT sum(total) FROM invoice_master WHERE invoice_master.customer_id = customers.customer_id) as total FROM customers; However I can't get that beast to work in SQLite. Many thanks! Kurt Welgehausen wrote: ...get all customers records, plus the sum of a column in the invoice... The idea is to get customer_id and the sum from the invoice table, then join that with the rest of the customer info. Of course, if you want to do it in one SQL statement, you have to write those steps in reverse order: select customers.*, ctots.total from customers, (select customer_id cid, sum(invoice_amount) total from invoice_master group by cid) ctots where customers.customer_id = ctots.cid If this doesn't make sense to you, try using a temporary table: create temp table ctots (cid integer primary key, total float) -- or whatever types are appropriate insert into ctots select customer_id, sum(invoice_amount) from invoice_master group by customer_id select customers.*, ctots.total from customers, ctots where customer_id = cid Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Join query help
So I'm sitting here in a pinch and my brain just refuses to work... 2 tables, a customer and an invoice table. What is the proper SQL to get all customers records, plus the sum of a column in the invoice table with a relation on the customer ID, but not all customers might have an invoice record.. Something like SELECT *,sum(im.total) as total FROM customers AS c, invoice_master as i WHERE i.customer_id = c.customer_id GROUP BY c.customer_id; Except that, of course, means that every customer has to have a record in the invoice table or they won't show in the list. I just want 0 to show for total for those customers that don't have a record in the invoice table. Much appreciation up front as I know this is probably one of those "duh" kinds of questions.. -- Mitchell - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] help with evaluation of sqlite db tool
Jim, all you need to be careful of is the license that accompanies the academic version. Most prevent the author from using the software for "commercial" purposes (that is generally defined within the license). If you are enrolled in school then you are a student so it wouldn't be dishonest to buy the software at the discounted price. If you intend on developing something to be used commercially just make sure you carefully read the academic license.. -- - Mitchell Vincent - kBilling - Invoice Software - http://www.k-billing.com Darren Duncan wrote: At 2:44 PM -0400 6/3/04, jim mcnamara wrote: I work part-time in an electronics department at a mass retailer. I better not be dishonest and buy academic software. I will look at python free or cheap tools and activestate unless I run across any other ideas. FYI, lots of students work part time to make ends meet. But if they attend classes in a semblence of full time then they are still students. Full time classes means only a small amount of time to work, vs full time work, meaning a lot less money earned. If you are taking a lot of classes then getting educational prices is perfectly fair. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Does Richard have a wish-list @ Amazon?
Well then, expect some books because SQLite has made me a lot of cash! Thanks Dr. Hipp - amazing work with SQLite! D. Richard Hipp wrote: Frederic Faure wrote: Does Richard have a wish-list over at Amazon or Barnes and Noble I have a wish-list on Amazon. :-) My name and email address at Amazon are as shown in the signature line below. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Newbie --question about multiple PCs accessing sqlite
> I'm contemplating using the TCL binding of sqlite, but the database is to (B> be located in a shared folder and about 100 users will be accessing it for (B> read/write purposes. I would like to take some time to learn as much as (B> possible to safeguard the database from getting corrupt due to several (B> users accessing it at the same time. However, the sqlite website (B> "http://www.sqlite.org/cvstrac/wiki?p=WhenToUseSqlite" says: (B> (B> $B!H(BA good rule of thumb is that you should avoid using SQLite in situations (B> where the same database will be accessed simultaneously from many computers (B> over a network filesystem.$B!H(B (B> (B> (B> I have 3 questions: (B> (B> 1.From the above statement, Am I right to assume that sqlite is not for (B> me? (B (BProbably not unless you intend on implementing some application level (Bwrite locking on the SQLite database. I've done this with one (Bapplication but there probably aren't ever anywhere near 200 people (Baccessing it at the same time. If someone is writing the database it (Bwaits a few milliseconds and tries again (up to a finite number of (Btimes, at which time it reports a failure).. (B (BWith that many possible concurrent users you might look into a RDBMS (Bserver like PostgreSQL or MySQL. My personal preference is PostgreSQL, (Bbut many use MySQL too.. (B (B> 2.If sqlite is feasible for my purpose, where can I find good (B> information to avoid ending up with a corrupt database (B (BSQLite locks the *whole* database when writing. You simply cannot write (Bmore than one thing at a time to the database (INSERT, UPDATE, DELETE (Betc). I don't think there is any documentation that says anything more (Bthan that. (B (BI'm not sure if doing so will corrupt the data or just plain fail, but (Beither way you can't do it :-) (B (B> 3.The TCL binding I downloaded says version 2.0 and I do not have a C++ (B> compiler, where can I get the most recent binary of TCL sqlite? (B> Thanks. (B (BNo idea on this one, check the SQLite webpage.. (B (B-- (B- Mitchell Vincent (B- kBilling - http://www.k-billing.com (B (B (B- (BTo unsubscribe, e-mail: [EMAIL PROTECTED] (BFor additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite and ODBC
Well put, Darren. Raymond, I would say that if you need ODBC then you need to use a different database (server) all together. SQLite fills the embedded database niche perfectly but it is not a replacement for MS-SQL, MySQl, PostgreSQl, Oracle or other database *servers*. If you need a RDBMS server you are better off using one instead of faking it with SQlite. PostgreSQL and MySQL both run on Windows and are free (well, PostgreSQL is free but who knows about MySQL these days.) Best of luck! Darren Duncan wrote: At 7:25 AM -0700 5/17/04, Raymond Irving wrote: I think SQLite should come standard with an odbc driver since ODBC is an "open standard" I disagree. Partly this is because D. Richard Hipp would then have to start certifying it like his own code and ensuring that it is always up to date with the core SQLite code, since they would get released together. That may be more responsibility than he wants. Second, SQLite was intended first and foremost for embedding, and for the large fraction of people that use it that way, a database networking layer like ODBC is not going to be used anyway. (That said, if SQLite was intended primarily for a client/server use, then I would more likely agree with you.) The networking code would significantly increase the size of the core distribution, as well as make it harder to test, as networks have a lot more variables to be concerned with than a local disk-based system does. By keeping the ODBC driver separate, those other people who see that as their specialty can focus on it on their own time table. Let each person focus on what they do best, and all that. Finally, while ODBC is very common, it isn't the only protocol for networking databases, and some people may prefer an alternative. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Problem with 2.8.13
I'm not sure why you're getting "... terms must [not] be non-integer constants ..." instead of "no such column ...", but I think the first thing you should do is run 'pragma table_info' on your view to see what the column names are. I'll be surprised if there's a column named p.product_name. It's a view that joins two tables. Other problems with your query: 1. You don't need to call lower(). The like operator ignores case. Doh, I didn't realize that! 2. It makes no sense to write '%%'. That gives exactly the same result as '%'. If you're trying to limit your results to 2-character strings, you want double underscore, not double percent. That is an automatically generated query and in that particular case the search string was empty (it would normally go between the %'s).. Thanks! -- - Mitchell Vincent - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
