RE: [sqlite] storing the tables in separate files

2007-04-11 Thread Samuel R. Neff
Not that I would suggest it, but you could create a separate database for each table and then attach the databases to a single connection to join data together. The indexes will need to be in the same database as the table they index. But why do you want each table in a different file? Sam

[sqlite] Performance analysis of SQLite statements

2007-04-12 Thread Samuel R. Neff
Are there any tools to help analyze the performance of components with a particular SQLite statement? I'm aware of the EXPLAIN option which can show what VBDE code was used to execute a statement, but afaik there is no way to tell the time each step took. Basically I want to know how long the

RE: [sqlite] Help with SQL syntax. Ticket #2296

2007-04-12 Thread Samuel R. Neff
Andy's answer and explanation is consistent with my experience and expectations too.. mostly from MSSQL and Access background. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C.

RE: [sqlite] Help with SQL syntax. Ticket #2296

2007-04-12 Thread Samuel R. Neff
Wouldn't implementation dependent mean it's not really standardized? The way I read it the query could still be considered legal in some dbms and not in others (which stinks). Besides, the current version of SQLite seems to match on the first tables names which is consistent with

RE: [sqlite] Help with SQL syntax. Ticket #2296

2007-04-12 Thread Samuel R. Neff
PROTECTED] Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296 On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > > Wouldn't implementation dependent mean it's not really standardized? The > way I read it the query could still be considered legal in some dbms and

RE: [sqlite] Still getting "Insertion failed because database is full." errors

2007-04-13 Thread Samuel R. Neff
I would suggest including the SQL that was being processed, including all parameters, in the error message. Even better would be to keep a log of all SQL messages sent--perhaps keep the last X calls in memory and when an error occurs log all of those calls and then the offending one. log4net

RE: [sqlite] Supporting databases larger than 2GB on FAT filesystems?

2007-04-13 Thread Samuel R. Neff
You can break up the db into multiple databases and attach them to the same connection. That would be easiest approach (as long as one individual table is not bigger than 2gb). Sam --- We're Hiring! Seeking a passionate developer to join our team

RE: [sqlite] Insert order maintained?

2007-04-17 Thread Samuel R. Neff
We got bit by this when moving from MSSQL 2000 to MSSQL 2005. MSSQL returned rows by default in PK order and one of our former developers depended on this so when the behavior changed in MSSQL 2005 (which is fine 'cause it wasn't documented or expected behavior) our app broke in unexpected ways.

RE: [sqlite] Optimize a query

2007-04-17 Thread Samuel R. Neff
afaik SQLite will only use one index per table so if you have a where clause "WHERE public = 1 and _rowid IN (...)" it will use an index on public and not _rowid. Swapping the where clause around should have a significant impact: select _rowid, public_id, vote_count,

[sqlite] PRAGMA short_column_names ignored when GROUP BY is used

2007-04-17 Thread Samuel R. Neff
It looks like short_column_names pragma is ignored when GROUP BY is used in a query. Is this considered expected behavior? I hope not.. :-) Thanks, Sam sqlite> pragma short_column_names; short_column_names -- 1 sqlite> pragma full_column_names; full_column_names

RE: [sqlite] Still getting "Insertion failed because database isfull." errors

2007-04-18 Thread Samuel R. Neff
One thing to note is that the SQLite.NET wrapper by default issues all transactions as "BEGIN IMMEDIATE" so if you're running the SELECT within a transaction it will be within the context of an exclusive transaction (that's what BEGIN IMMEDIATE means, right?). You can override this by using

RE: [sqlite] Efficiency and Pragmas

2007-04-18 Thread Samuel R. Neff
cache is per-connection so if you open and close connections a lot then a large cache won't help your program. The command line app is a single connection so a large cache there will help (although not with the first queries--only subsequent ones). Synchronous off is dangerous. Search the

RE: [sqlite] Still getting "Insertion failed because database isfull." errors

2007-04-18 Thread Samuel R. Neff
Another option is to change the SQLite.NET wrapper to automatically retry on SQLITE_FULL error similar to the way it handles a schema error. Then it would be transparent to your app. It would have to close and reopen the connection of course, not just retry, but still the solution is manageable

RE: [sqlite] Prefix searching for fts2.

2007-05-01 Thread Samuel R. Neff
This is great! The main reason we decided not to use FTS in our project was lack of prefix searching. With this new functionality we'll probably switch to using FTS in a future update. One suggestion though, instead of (or in addition to) using '*' as the prefix operator perhaps '%' would be

RE: [sqlite] FW: Performance problem with complex where clause

2007-05-02 Thread Samuel R. Neff
Will the original poster still run into performance problems where sqlite will only use one index per table so if targetid matches on a ton of rows sqlite has to scan them all for the matching sourceid? Perhaps a multi-column index would be appropriate here to index both "target.id" and

RE: [sqlite] Transaction journal corrupted by antivirus

2007-05-03 Thread Samuel R. Neff
Most anti-virus software allows you to specify an exception folder and/or file. Tell the anti-virus to ignore sqlite db and the journal. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the

RE: [sqlite] Search on Age, from DOB column

2007-05-03 Thread Samuel R. Neff
You'll be better off converting the target age back to a date and then search for the date. That way SQLite can use an index in your query (it can't use an index when the filter is on an expression). HTH, Sam --- We're Hiring! Seeking a passionate

RE: [sqlite] perfomance degradation for expr "foo = X or bar =X"

2007-05-09 Thread Samuel R. Neff
I wonder if it would be beneficial to add an additional where clause which can prefilter the data so you only need to perform the full calculation on a subset of records. I haven't done the math, but let's supposed that point_x is 10 and that for any result of your long calculation to be true,

RE: [sqlite] Longest "real" SQL statement

2007-05-10 Thread Samuel R. Neff
We don't do it in SQLite but as an example of how large a legitimate SQL statement can be, in a previous project we generated a pseudo-cube from the current database in a single sql statement. Basically we wanted to implement a fast complex search routine where users can choose any fields from

[sqlite] Many small queries vs fewer large queries

2007-05-14 Thread Samuel R. Neff
Traditionally we've found that it's better to issue a few larger queries against a database (such as MSSQL or Oracle) even when the results required some processing to separate out the data because much of the cost of running a query was communication and networking overhead. Since SQLite is an

RE: [sqlite] Limit selection by rolling sum?

2007-05-14 Thread Samuel R. Neff
filter on a nested select. select id, ( select sum(i2.size) from items i2 where i2.id <= i1.id ) sum_size from items i1 where sum_size < 5; I'm sure performance sucks :-) Something like this would be much faster to do in a

RE: [sqlite] Why doesn't this UPDATE work?

2007-05-25 Thread Samuel R. Neff
Try this UPDATE table1 SET column3 = ( SELECT column3 FROM table2 WHERE table2.column1 = table1.column1 AND table2.column2 = table1.column2) HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team

RE: [sqlite] Re: CAST

2007-05-29 Thread Samuel R. Neff
Actually I'd say he gave a great explanation of why the wrapper approach is so important. Robert went through all the work to make SQLite perform in a scenario compatible with many other databases so now the users of his wrapper don't have to. Saying not to use wrappers when programming in

RE: [sqlite] Re: CAST

2007-05-30 Thread Samuel R. Neff
SQLite's typelessness is an asset if you work only with SQLite but in any application that uses multiple database engines of which SQLite is only one supported engine, the non-standard typelessness is something that has to be worked around. I for one would be in favor of an option to enforce

RE: [sqlite] Re: CAST

2007-05-30 Thread Samuel R. Neff
afaik strict affininity mode hasn't been implemented. >From http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq " Q) How can the strict affinity mode be used which is claimed to exist on http://www.sqlite.org/datatype3.html A) This has not been implemented as of version 3.3.13. " Sam

RE: [sqlite] Concurrency

2007-06-01 Thread Samuel R. Neff
If option (b), using a single thread for writing and a multi-threaded write queue works in your situation, then that would probably provide best concurrency and performance. The only downside to this is the delayed writes mean you don't as easily get feedback to the original writer if a write

RE: [sqlite] Concurrency

2007-06-01 Thread Samuel R. Neff
Nice analogy, but in the case the cat really does have 9 lives (or many more) 'cause with SQLITE_BUSY you can just retry and while retrying is a performance penalty in my experience SQLITE_BUSY is a very rare occurrence. All I'm saying is don't fix a perceived problem until you've tested to be

[sqlite] Does Attach improve Concurrency ?

2007-06-01 Thread Samuel R. Neff
When you have a connection with multiple attached databases and the connection acquires an exclusive lock, does it always lock all attached databases or does it keep track of which databases require the lock? Does using separate databases and attaching them improve concurrency (by providing

RE: [sqlite] extracting and comparing dates

2007-06-04 Thread Samuel R. Neff
SQLite doesn't have any internal notion of a date, only numbers or strings. You can choose to store dates as numbers and SQLite has some conversion functions to help you work with dates. Personally I store dates as strings. These are not properly formatted dates: 1997-6-17 1998-5-6 1997-6-24

RE: [sqlite] Why do you use SQLite? Comments for an article needed

2007-06-07 Thread Samuel R. Neff
We chose SQLite for many reasons: - zero configuration/installation - availability of wonderful ADO.NET 2.0 wrapper - easily extensible with custom functions - performance (4x faster than MSSQL in our tests) - available commercial support The only thing we don't like is the lack non-standard

[sqlite] Data structure for versioned data

2007-06-20 Thread Samuel R. Neff
Not specific to SQLite, but we're working on an app that needs to keep versioned data (i.e., the current values plus all previous values). The versioning is integral to the app so it's more than just an audit trail or history. Can anyone share experiences with the database structure for this

RE: [sqlite] Introducing... ManagedSQLite

2007-06-25 Thread Samuel R. Neff
Daniel, We already have an excellent ADO.NET compliant provider for SQLite that Robert mentioned. What advantages does your wrapper provide of the existing one? SQLite.NET already provides full ADO.NET support including custom functions and collation sequences written in any .NET languages and

RE: [sqlite] How to sort not binary?

2007-06-25 Thread Samuel R. Neff
Since you use C# (mentioned in a different message) you can easily write a custom collation function with SQLite.NET. http://sqlite.phxsoftware.com/ Look in the help for SQLiteFunction and particularly FunctionType.Collation. HTH, Sam --- We're

RE: [sqlite] Sqlite - LISTEN/NOTIFY

2007-06-28 Thread Samuel R. Neff
You could achieve this with a trigger + custom function. MSSQL 2005 now has this ability (though no SQL syntax applies, it's built into the ADO.NET 2.0 provider) and it's a really nice feature. HTH Sam --- We're Hiring! Seeking a passionate developer

RE: [sqlite] Sparse Data Sets

2007-06-29 Thread Samuel R. Neff
Yes, there will be a performance hit 'cause when you access a column SQLite will loop through the columns in the row to find the target column. If the data is that sparse then I would suggest a different format. One that I've used a lot and have been pleased with is the following: Results -

RE: [sqlite] Multi-column "in" clause supported?

2007-06-29 Thread Samuel R. Neff
You can do it with a JOIN instead of IN and I'm pretty sure it will still use an index. SELECT COUNT(*) FROM guid_version_map M LEFT JOIN latest_version V ON M.guid = V.guid AND M.version = V.version WHERE

RE: [sqlite] attached databases

2007-07-04 Thread Samuel R. Neff
Use UNION to run queries against each db and return a single result. 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]

RE: [sqlite] Using SQlite with .NET

2007-07-06 Thread Samuel R. Neff
There are a few .NET wrappers for SQLite. I would suggest System.Data.SQLite available here: http://sqlite.phxsoftware.com/ And for ADO.NET 2.0 development use version 1.0.43. For LINQ stuff use 2.0.35. Also wrapper-specific questions will probably get quicker responses in their dedicated

RE: [sqlite] How to implement connection pooling

2007-07-10 Thread Samuel R. Neff
If you're using a recent version of MONO then I assume you're using System.Data.SQLite from Robert Simpson which is now bundled with MONO, right? I use a custom version of the same provider which has connection pooling implemented in the wrapper. We've found it to be a huge performance

RE: [sqlite] FTS and upgrades

2007-07-10 Thread Samuel R. Neff
Even without having FTS1 loaded, can't you delete the *_content and *_term tables directly and that would be effectively the same as deleting the virtual table? Sam --- We're Hiring! Seeking a passionate developer to join our team building products.

RE: [sqlite] Binding vs No Binding

2007-07-12 Thread Samuel R. Neff
I think you'll see the biggest difference when you run the same statement many times with different bound variables (vs recompiling each time). Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the

RE: [sqlite] Does Transaction object roll back automatically on exceptions?

2007-07-16 Thread Samuel R. Neff
Rollback is automatic if no Commit is issued in SQLite.NET. If you want custom logic, such as including additional exception information, then use try/catch BeginTransaction(); try { .. Commit(); } catch(Exception ex) { RollBack(); throw new Exception("An exception occurred and the

RE: [sqlite] Does Transaction object roll back automatically on exceptions?

2007-07-17 Thread Samuel R. Neff
Note that by default the ADO.NET wrapper executes transactions in immediate mode which is not desirable for read-only data. To start a deferred transaction, you need to use the SQLite.NET-specific overload BeginTransaction(true) which is not available if using the DbProvider object model. Best

RE: [sqlite] How many table can i create in a db?

2007-07-20 Thread Samuel R. Neff
SQLite parses the schema every time you open a new connection so the more complex the schema the longer it will take to connect. We have 74 tables in our database with a lot of triggers and it takes 17ms to open a connection. So even if it will let you create 10,000, the performance impact of

RE: [sqlite] fts2 in the amalgamation source?

2007-07-26 Thread Samuel R. Neff
Unless something changed recently that I missed, fts2 is not a standard part of sqlite so including it in the standard amalgamation would be a big change. Having two distributions, one with and one without, might make sense though. Or including it with an OMIT flag would work too (default to

RE: [sqlite] how do i generate a uniqueidentifier ?

2007-07-27 Thread Samuel R. Neff
I don't think there's any built-in way but you can create a custom function for it pretty easily. Are you using sqlite directly or a wrapper? Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the

[sqlite] SQLite roadmap?

2007-08-03 Thread Samuel R. Neff
Is there a roadmap of major planned features in upcoming releases? I didn't see anything on the wiki or site.. Thanks, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro

RE: [sqlite] Is SQLite Case Sensitive?

2007-08-07 Thread Samuel R. Neff
Use of either "OR" or "Lower/Upper" will bypass any index and force a full table scan. Much better to use COLLATE NOCASE instead or a custom collation if you need internationalized comparisons. Sam --- We're Hiring! Seeking a passionate developer to

RE: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

2007-08-14 Thread Samuel R. Neff
+1 for fts3 or fts2_1 :-) --- 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: Scott Hess

RE: [sqlite] why doesn't this work? (fts rowids)

2007-08-15 Thread Samuel R. Neff
With FTS3 can you specify the rowid to use in SQL or is it always automatic? It seems like most commonly you'd want the FTS data to match up with a real table using the same key and not have to store the FTS key in a separate table. Ideally I'd want to be able to include a single foreign key

RE: [sqlite] Problem loading extension in Delphi (FTS2)

2007-08-16 Thread Samuel R. Neff
loadable extensions are not required to create custom functions, and having access to source is not required for custom functions either. SQLite.NET provides very clean support for custom functions written in any .NET language and they are loaded automatically by the wrapper from any DLL present

RE: [sqlite] To increase search speed

2007-08-20 Thread Samuel R. Neff
Method 3, normalization, is the right route but I think the implementation needs a little more work. First don't store both Artist ID and Artist Name in the Music table--only store the ID. This goes for AlbumID/Name and GenreID/Name as well. Then reorder the columns to put the integers first in

RE: [sqlite] FTS2 Question again Python + .NET

2007-08-20 Thread Samuel R. Neff
" I'm having the same problem with .net, cant find a function which does this... " Which .NET wrapper are you using? System.Data.SQLite has FTS2 precompiled. http://sqlite.phxsoftware.com Sam --- We're Hiring! Seeking a passionate developer to join

RE: [sqlite] New Operator Support

2007-09-07 Thread Samuel R. Neff
Wouldn't it be a lot easier to just create a custom function? What's the advantage (other than pretty syntax) of using a custom operator? Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the

RE: [sqlite] Adding additional operators to FTS3

2007-09-14 Thread Samuel R. Neff
The /10 syntax makes sense to programmers but I think users are going to forget it pretty quickly. Same with "OR" an "NEAR" being required to be all caps (I didn't know that). Ideally the UI an application exposes would show the user that OR and NEAR were interpreted as keywords and not tokens

RE: [sqlite] Filesystem Layer problems

2007-09-18 Thread Samuel R. Neff
If you only need Windows compatibility you can use the System.Data.SQLite port. It's made for ADO.NET but it is also binary compatible with SQLite and can be used from C code. It includes Windows-specific encryption and is free. http://sqlite.phxsoftware.com/ With the ADO.NET stuff it's

RE: [sqlite] multiple databases

2007-09-19 Thread Samuel R. Neff
Most likely if you're simulating networks where there is a db in each node then you really want to simulate network traffic based on that db protocol. SQLite is an embedded database that runs in-process and by definition has no network traffic or protocol (except if you count opening a database

RE: [sqlite] SQLite.Net

2007-09-19 Thread Samuel R. Neff
Michael, I haven't used the wrapper you mentioned so I can't help with the specific problem in that wrapper. However, I do use the one Robert suggested heavily and can attest to the fact that it is extremely well written and works without errors. It's also an ADO.NET implementation making the

RE: [sqlite] SQLite.Net

2007-09-20 Thread Samuel R. Neff
is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Yves Goergen [mailto:[EMAIL PROTECTED] Sent: Thursday, September 20, 2007 8:34 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite.Net On 19.09.2007 17:54 CE(S)T, Samuel R. Neff wrote

RE: [sqlite] SQLite.Net

2007-09-20 Thread Samuel R. Neff
sqlite.org Subject: Re: [sqlite] SQLite.Net On 20.09.2007 15:06 CE(S)T, Samuel R. Neff wrote: > can you be more specific? Thread links.. http://sqlite.phxsoftware.com/forums/t/731.aspx "SQLiteDataReader.GetValue() not returning DateTime" http://sqlite.phxsoftware.com/forums/t/79

RE: [sqlite] Seeking advice on On Demand Loading

2007-09-28 Thread Samuel R. Neff
I've found that the best trade-off in performance and memory for on-demand loading is to first run a query which retrieves all the id's of the items you want in the list and store the list in memory. Then you can use that to run a second query for full data using a where clause with "ID IN

RE: [sqlite] Getting an Array or list in a select statement...

2007-10-11 Thread Samuel R. Neff
We use a custom aggregate function called DisplayList to do exactly what you're talking about. C# code follows. SELECT U.UserName, DisplayList(R.RoleName) FROM Users U INNER JOIN Xref_Users_Roles X ON U.UserID = X.UserID INNER JOIN Roles R ON X.RoleID

RE: [sqlite] how to get file handle from sqlite3 object?

2007-10-11 Thread Samuel R. Neff
the question of how to do a backup comes up a lot so this would be very nice. If it was added with a compile-time OMIT flag then there shouldn't be a big concern on bloating the library. Sam --- We're Hiring! Seeking a passionate developer to join our

RE: [sqlite] Help with an unusual query

2007-10-24 Thread Samuel R. Neff
This can be done with a custom aggregate function. I posted an example a week or so ago here in the list (example in C#). SELECT key, DisplayList(data) GROUP BY Key where DisplayList() is a custom function that concatenates it's values. I don't think this can be done in straight SQL. HTH,

RE: [sqlite] Impact of no.of tables and records on Startup time

2007-10-25 Thread Samuel R. Neff
Complexity of the schema affects time required to open a connection since the schema has to be read and processed. With about 70 tables each with lots of indexes and triggers, it takes us 17ms to open the connection. HTH, Sam --- We're Hiring! Seeking

RE: [sqlite] How many virtual table implemenations are there out there?

2007-11-02 Thread Samuel R. Neff
I like the term virtual 'cause that's exactly what they are.. a table that does not really exist in the db and is provided by some other system. This is not inconsistent with other DBMS's which use terms like "virtualized view", both are tables that are not linked to underlying physical data.

RE: [sqlite] How use "uSQLiteServer" wrapper with Visual Basic .NET ???

2007-11-06 Thread Samuel R. Neff
uSQLiteServer provides it's own network protocol implementation and it's own API so using is nothing like using SQLite itself or any other SQLite wrapper. If you feel you need to use uSQLiteServer then you'll need to either use the C code provided for it's client API, convert it to VB.NET, or

RE: [sqlite] Is possible put a password in database ?

2007-11-08 Thread Samuel R. Neff
Paulito, I believe from a previous post you're using the System.Data.SQLite wrapper, right? That provides Windows-only encryption built in to the wrapper. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based

RE: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Samuel R. Neff
I hope this doesn't offend, but perhaps the best solution is to outsource the website to someone or a company that specializes in websites and design (with your stated simplicity goals in mind of course). We certainly wouldn't want a graphic designer hacking away at the SQLite engine, so isn't

RE: [sqlite] Request for help with the SQLite Website

2007-11-13 Thread Samuel R. Neff
I think the "about" text misses some of what, to me, are the most important parts of SQLite - in-process - zero maintenance Also as a .NET developer I would be put off by the "C-Library" reference. SQLite works very well in many languages regardless of the fact that it's written in C. I would

RE: [sqlite] Request for help with the SQLite Website

2007-11-14 Thread Samuel R. Neff
Limiting the width is good, but the pixel-based limit can cause variations on different resolutions and font settings. I would suggest this instead: max-width: 60em; Which will cause the max width to adjust based on text size settings. With the most recent change, I feel overwhelmed

RE: [sqlite] Request for help with the SQLite Website

2007-11-15 Thread Samuel R. Neff
The vast majority of database engines run as separate services on a machine and clients communicate with them through a network protocol. SQLite runs in the same process as the host application either as a statically linked or a dynamically loaded library and shares memory space of the client.

RE: [sqlite] Would SQLite be a good choice

2007-11-16 Thread Samuel R. Neff
You could maintain a queue in memory of all the data to be written, have each page view queue up the new data and have a single db writer thread that dequeues items and writes to the db. That way you get the benefit of writing directly to the db, but do not have the extra overhead on each page

RE: [sqlite] Would SQLite be a good choice

2007-11-16 Thread Samuel R. Neff
Most languages have the ability to kick off different threads that run in the background. You can have a writer thread that dumps the queue and then sleeps for another minute and then continues the loop. I don't know PHP, but a quick search found that it does have a Thread class which I

RE: [sqlite] SQLite does not support multi-row inserts?

2007-12-04 Thread Samuel R. Neff
MS SQL 2008 will support multi-row insert statements too. http://richardsbraindump.blogspot.com/2007/07/what-new-in-sql-2008-katmai.ht ml Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in

RE: [sqlite] SQLite is in Android

2007-12-05 Thread Samuel R. Neff
One of my co-workers was playing around with SQLite on his iPhone and was able to access data including contacts and call log and pretty much everything. It's a SQLite database and not encrypted. Sam --- We're Hiring! Seeking a passionate developer to

RE: [sqlite] Querying DATE column with date/time string.

2007-12-06 Thread Samuel R. Neff
'between' will work fine with your situation, you just have to use between '2008-01-01' and '2008-01-31 23:59:59.99' or even better between '2008-01-01' and '2008-01-31Z' HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team

RE: [sqlite] Any advantages of "varchar()" over "text"?

2007-12-07 Thread Samuel R. Neff
-Original Message- From: P Kishor [mailto:[EMAIL PROTECTED] Sent: Friday, December 07, 2007 5:50 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Any advantages of "varchar()" over "text"? I may be wrong, but my understanding is that other than INTEGER PRIMARY KEY, SQLite doesn't

RE: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Samuel R. Neff
MSSQL results match your MySQL and PostgreSQL results. (I only changed the table name to be a temporary table) create table #t1(a INT, b INT, c INT); insert into #t1 values(1, 2, 4); insert into #t1 values(2, -1000, 5); (1 row(s) affected) (1 row(s) affected) -- See if select alias

RE: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?

2007-12-13 Thread Samuel R. Neff
Steve, I found the information you posted to be a good contrast and would love to learn more, but you didn't include any technical details. You said you have atomic commits without a rollback journal and instead use some revolutionary new way of doing commits. You said DeviceSQL performs

RE: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Samuel R. Neff
That also brings up the simple solution in that if you know you're db is going to be in the 200GiB range, declare a larger page size before creating the DB. Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based

RE: [sqlite] Should the next release be 3.5.4 or 3.6.0?

2007-12-13 Thread Samuel R. Neff
3.5.4 --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL

RE: [sqlite] DeviceSQL

2007-12-14 Thread Samuel R. Neff
-Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 3:55 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DeviceSQL ... is it not directly comparable to DeviceSQL unless the external compiler handles not only SQL but also PL/SQL. The

[sqlite] table.* excludes ID field when using natural join ?

2007-12-17 Thread Samuel R. Neff
I would expect "SELECT T.*" to always return all fields from table T. However this seems not to be the case when using natural join. Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\>sqlite3 SQLite version 3.4.2 Enter ".help" for instructions sqlite> create

RE: [sqlite] suggestion for an optimized sql

2007-12-20 Thread Samuel R. Neff
You could create a field in the table Value01LessThanValue02 and use a trigger to update this value whenever data is updated. Then you can search on just this one field. However, it's a boolean result so depending on the percentage of records that match this condition, the index may not be that

RE: [sqlite] EXISTS and NULLs

2008-01-02 Thread Samuel R. Neff
This behavior is consistent with MSSQL. EXISTS returns true for NULL fields in MSSQL 2005. Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested

RE: [sqlite] no mention of SQLite :(

2008-01-07 Thread Samuel R. Neff
I got this when I tried to post a comment about missing SQLite in the article.. "* You have been banned from posting. If you feel this is an error, please email [EMAIL PROTECTED] " I've never posted on eWeek before so this is upsetting. Sam --- We're

RE: [sqlite] free excel-like COLORFUL gui for sqlite

2008-01-08 Thread Samuel R. Neff
SQLite is not an end-user tool (nor is any other database except *perhaps* Access, but even then it just gets people into trouble). What you want is not a free gui for SQLite but a custom application that does what the user needs and uses SQLite as it's data storage mechanism. This certainly

RE: [sqlite] SQLite --> PostGres

2008-01-11 Thread Samuel R. Neff
That's funny, we're currently in the process of upsizing our application from a MSSQL to SQLite. :-) Besides, I don't think it's ever good to encourage people to develop on one platform with the intent on deploying to another. I've seen people do it with Access -> MSSQL and MSSQL -> Sybase

RE: [sqlite] SQLite --> PostGres

2008-01-11 Thread Samuel R. Neff
The same could be said for pretty much any other database.. they're all similar 'cause they all follow (to some extent) the same standard. If aliases were defined for PostGres then why not for MySQL, Oracle, MSSQL, Firebird, VistaDB, SAP/DB, DB/2, and on and on. I don't agree that defining

RE: [sqlite] Aggregates in SELECT without GROUP BY

2008-01-14 Thread Samuel R. Neff
I've run into this issue myself and had more trouble than necessary tracking down problems related to it. Personally I would consider it a bug, but it's been discussed hear as accepted behavior. Sam --- We're Hiring! Seeking a passionate developer to

RE: [sqlite] WHERE SomeColumn IN (@SQLiteParameter) ???

2008-01-15 Thread Samuel R. Neff
Since you need notification of data so quickly, perhaps it would be better to use some type of notification table that indicates when new data is available and a trigger to populate this table. Then you can query SELECT MAX(ID) FROM Notifications which is ridiculously fast. HTH, Sam

RE: [sqlite] startswith and contains

2008-01-24 Thread Samuel R. Neff
You're right, % is standard. MS Access used * and more recently supports both * and %. I'm not aware of any other DB that supports using * as wildcard for LIKE. It's in the docs, but is kinda buried in the middle of this page: http://sqlite.org/lang_expr.html Sam

RE: [sqlite] Foreign Constraint Triggers Across Attached Databases

2008-01-24 Thread Samuel R. Neff
I've run into two situations recently where I would have preferred to write triggers across databases. Both related to audit tracking of data. The first situation is that for every table, I have a corresponding history table that records the history of every record. So let's say I have CREATE

RE: [sqlite] Number of elements in IN clause

2008-01-28 Thread Samuel R. Neff
: Re: [sqlite] Number of elements in IN clause "Samuel R. Neff" <[EMAIL PROTECTED]> wrote: > I don't think it is standard SQL. At the very least, it doesn't work in > MSSQL. Standard is > > SELECT * FROM maintable WHERE key IN (select x from stuff); > >

RE: [sqlite] Number of elements in IN clause

2008-01-28 Thread Samuel R. Neff
I don't think it is standard SQL. At the very least, it doesn't work in MSSQL. Standard is SELECT * FROM maintable WHERE key IN (select x from stuff); SQLite shortened version is much nicer.. wish it was standard. Sam --- We're Hiring!

RE: [sqlite] How to make correct transaction use only SQL?

2008-01-30 Thread Samuel R. Neff
Instead of piping the sql into sqlite3.exe, use the ".read" command instead. C:\Temp\s>sqlite3 test.dat SQLite version 3.4.2 Enter ".help" for instructions sqlite> .read test.sql SQL error near line 10: column id is not unique SQL error near line 12: cannot commit - no transaction is active

RE: [sqlite] Using LIKE to check the first digits?

2008-02-01 Thread Samuel R. Neff
If you need LIKE 'abc%' and for it to not be case insensitive, then LIKE is preferred. Also, LIKE is standard SQL so if you're writing SQL that has to run across different vendors, you'd use LIKE. Sam --- We're Hiring! Seeking a passionate developer to

Re: [sqlite] Why attach databases?

2008-02-07 Thread Samuel R. Neff
It's particularly valuable when you want to run queries across databases. INSERT INTO main.table SELECT * FROM newdata.table; Also if it's possible for you to segment out your data to multiple databases but normally only work with one of them, then you can increase performance and concurrency

Re: [sqlite] Why attach databases?

2008-02-07 Thread Samuel R. Neff
A blanket "stay away" is pretty extreme. I would rather be able to have triggers across attached databases, but without that will use them less, but still there are times when they're very useful and you have no need for triggers. Sam --- We're Hiring!

  1   2   >