[sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

2014-10-07 Thread Stephen Chrzanowski
I'm making up a small database (for yet another tool I never plan on releasing) and during the table creation, I had a thought about the "Not Null" and "On Conflict" resolution mechanism. When adding a NULL value to a table that has the NOT NULL flag set on that field, instead of raising an

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

2014-10-08 Thread Stephen Chrzanowski
On Wed, Oct 8, 2014 at 1:26 AM, Simon Slavin wrote: > > You could probably use a TRIGGER that detects the type of row you don't > want and replaces it with your preferred form. This doesn't do exactly > what you want, but it is something like it. > > That'd work, but extra

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

2014-10-08 Thread Stephen Chrzanowski
Oct 8, 2014, at 6:14 AM, Stephen Chrzanowski <pontia...@gmail.com> > wrote: > > > When adding a NULL value to a table that has the NOT NULL flag set on > that > > field, instead of raising an exception, if the field definition were to > > have the word "USE" b

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

2014-10-09 Thread Stephen Chrzanowski
I wouldn't call it 'magical' if the definition is right on the field declaration, and depending on the 'worth' of that particular bit of data (Booleans in this case, and I've got absolutely zero concern to the actual state of said boolean, but taking into consideration the typeless nature of

Re: [sqlite] Single large table vs. several smaller tables

2014-10-09 Thread Stephen Chrzanowski
I'm assuming you're measuring something from each unit (I'm guessing device?) out on the field? Taking a quick glance at your table, I could see three tables being created to normalize things out. You have particular units at a particular location. That can be one table. Another table would be

Re: [sqlite] Stored Procedures

2014-10-09 Thread Stephen Chrzanowski
Views, yes. Stored Procedures, no. On Thu, Oct 9, 2014 at 1:04 PM, Omprakash Kolluri wrote: > Hi, > > I am new to SQLite. I am working on an app that I am developing and plan to > use SQLite as an embedded database. My Question - Does SQLite support > stored procedures

Re: [sqlite] Make a database read-only?

2014-10-15 Thread Stephen Chrzanowski
I've got three options, two of which require an internet connection, one part time, the other full time. The third option has the constraint on the size of the data in question. - Have your preference of a resultant hash check in a plain text file sitting somewhere on your web server. The

Re: [sqlite] Search query alternatives.

2014-10-15 Thread Stephen Chrzanowski
Careful with the timing. You may be looking at OS memory caching the result set instead of pulling from the drive. For best bets, either re-run both queries several times, ditch the longest and shortest times, then take the mean or average times and do the comparison that way. On Wed, Oct 15,

Re: [sqlite] Any tips on reducing memory requirements for small MCU?

2014-10-20 Thread Stephen Chrzanowski
I have no idea what is 100% required in the database, but I THINK you can include some compiler directives that REMOVE certain features. I know FTS is an optional thing, but I don't know if it is included as part of the default build or not. CTE (I think that is it?) might also be something you

Re: [sqlite] Full outer joins

2014-10-22 Thread Stephen Chrzanowski
Saying I 'would like this type of join' is something I say very lightly. By that I mean it'd be good to see them, but, really, I'm not going to put any pressure on anyone to get it implemented. I'd been using these joins in SQL2K for years before I found out about SQLite and a lot of what I was

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Stephen Chrzanowski
Make sure H11 has an index on it. Also ensure that ToricCY doesn't have blobs attached to it. Throw the blobs into a different table and use a 1:1 relationship to link them, then only pull the blobs when needed. SQLite apparently will load an entire row of data out of the database, even if the

[sqlite] Minor Documentation Issue - Virtual Tables

2014-10-25 Thread Stephen Chrzanowski
https://www.sqlite.org/vtab.html I was trying to understand what Virtual Tables are, and how they'd benefit me in a new app I'm building, but I noticed that the numbering system on the page is incorrect, or, they slipped something into my coffee this AM. (I should thank them if they did!) The

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Stephen Chrzanowski
Ahh.. Thanks Richard. So if you were to have blobs live at the front of the row, it'll have to read through that blob to get that byte in the next field, correct? On Sat, Oct 25, 2014 at 8:31 AM, Richard Hipp <d...@sqlite.org> wrote: > On Sat, Oct 25, 2014 at 8:15 AM, Stephen Ch

[sqlite] Inserting a row with all defaults set in table

2014-10-25 Thread Stephen Chrzanowski
I've got a table that has defaults set for all fields CREATE TABLE [tEvents] ( [EventID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Airline] CHAR DEFAULT '', [TicketID] INTEGER DEFAULT 0, [Resolved] BOOL DEFAULT 0); Seems to me it'd be a bit redundant to do an "insert into tEvents

Re: [sqlite] Inserting a row with all defaults set in table

2014-10-25 Thread Stephen Chrzanowski
Perfect. Thanks guys. On Sat, Oct 25, 2014 at 10:03 AM, Simon Davies <simon.james.dav...@gmail.com > wrote: > On 25 October 2014 14:49, Stephen Chrzanowski <pontia...@gmail.com> wrote: > > I've got a table that has defaults set for all fields > > > > CREATE

Re: [sqlite] Question on locks

2014-10-25 Thread Stephen Chrzanowski
You did that per connection? On Sat, Oct 25, 2014 at 10:02 AM, Ali Jawad wrote: > Thanks for the input, I did add PRAGMA busy_timeout=1500; before any query > but I am still getting loads of locked database errors, please advice >

Re: [sqlite] Finding second occurrence of character in string

2014-10-26 Thread Stephen Chrzanowski
Another method beyond what was suggested above/below would be that since the / could be considered a delimiter, you could consider each field a word and insert each word into a separate table and index each word. Have another table reference the indexed word to match whatever table you've

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-26 Thread Stephen Chrzanowski
If you're asking "if a field on a row contains a value that was previously defined a value due to the DEFAULT value in the schema, would this value be changed if the in the schema later changed", then no, it doesn't change. There is no reference to the default value in the schema once the data has

Re: [sqlite] Access Temp Tables/Views in background thread

2014-10-28 Thread Stephen Chrzanowski
As mentioned, temp tables are connection based, not database based. I can think of two ways of getting around this. Expose your child thread database object to your main thread. Then have your main thread do what it needs to do with it. I wouldn't entirely recommend this as it breaks scope,

Re: [sqlite] SQLite where clause tree

2014-11-10 Thread Stephen Chrzanowski
>From a mathematical standpoint in your example, going back to grade 4 math (35 years ago for me. *sigh*. I'm so sad), where clause works based off of standard order of operations based on BEDMAS and eventually working things down to booleans. In your example, the math would be processed as

Re: [sqlite] sqlite Issue

2014-11-14 Thread Stephen Chrzanowski
If you're using SQLite3.exe (or equivalent CLI - Command Line Interface) then by default the database id written to memory, not to the disk. Doing something like [ sqlite3.exe test.db3] will create a test.db3 file once you do an actual transaction like creating a table. I THINK even doing a

Re: [sqlite] Network and concurrency

2014-11-26 Thread Stephen Chrzanowski
Try? Don't use a network. It isn't safe due to file locking mechanisms (As you've noted) at the server side, not the client side. The other option is to either build a SQLite server where the local database store is, or, get MySQL/MSSQL up and running. https://www.sqlite.org/whentouse.html

Re: [sqlite] "database is locked" for SQLITE_BUSY

2014-12-03 Thread Stephen Chrzanowski
Although I think there is already an error result, one situation might be when the DB is in a read only state. On Wed, Dec 3, 2014 at 6:15 AM, RSmith wrote: > > On 2014/12/03 13:00, Jonathan Moules wrote: > >> Hi, >> Just a quick request/suggestion. >> >> Currently

Re: [sqlite] "database is locked" for SQLITE_BUSY

2014-12-03 Thread Stephen Chrzanowski
be missing a few tidbits of information both in what I've written above, as well as the mental think-through when I wrote it. :] On Wed, Dec 3, 2014 at 9:23 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski <pontia...@gmail.com> wrote

Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

2014-12-07 Thread Stephen Chrzanowski
You didn't provide the data set, so it'll be a guess. You'll need to look at the data and do some mental work on comparing what you expect versus what your query result comes back with, and prove what is in the database is what your query is EXACTLY asking for. (Returning on last names of

Re: [sqlite] backup fails despite large timeout

2014-12-11 Thread Stephen Chrzanowski
That'd depend on the size of the database, the medias speed you're writing to, and what kind of actions are happening during the backup. If your file size is in the GB range, and you're transferring over a 100mbit switch, if you have a SINGLE write per minute, your backup is going to restart.

Re: [sqlite] Whish List for 2015

2014-12-21 Thread Stephen Chrzanowski
For your second point, SQLite4 "may" never be released, or if it is going to be, not for a few years yet. 4 is a toy for the devs to try things out without borking things up in 3. 3 is being used by millions (or is it billions?) of devices and applications that the dev team is (very right) in

Re: [sqlite] sqlite login password

2015-01-03 Thread Stephen Chrzanowski
Maybe the question is "How do I make SQLite do this when accessing a database?" and the 3 just got dropped inadvertently. If that is the case, as Dr. Hipp said, SQLite has never done it, and I'll add on that it has never done it "stock". On the other hand, that link you posted, Dr. Hipp, is

Re: [sqlite] A new to-be User!

2015-01-05 Thread Stephen Chrzanowski
Although SQLite can be used in a server type situation, it is more geared towards being an embeded database solution. A couple of links you should read over: - http://sqlite.org/whentouse.html - http://sqlite.org/howtocorrupt.html On Mon, Jan 5, 2015 at 8:04 PM, Saffa Kemokai

Re: [sqlite] Fsync or fdatasync performs slowly

2015-01-18 Thread Stephen Chrzanowski
What kind of times are you looking at, and, what is the data being written to? On Sun, Jan 18, 2015 at 9:49 PM, Wei, Catherine wrote: > Hi, I'm running a software in a set-up box with linux system and find > that every time when I commit transaction, sqlite takes too

Re: [sqlite] System.Data.SQLite - Exception Calling SQLiteModule.DeclareTable

2015-01-23 Thread Stephen Chrzanowski
In the 8.3 days, I routinely gave directories an underscore as a delimiter for version information, prior to my actually using a version control package. So "game" would be the main thing, and if I wanted to test, "game_1" became the new WIP folder. If I liked what I did, I'd move "game" to

Re: [sqlite] "database disk image is malformed" error occurs more

2015-01-29 Thread Stephen Chrzanowski
On Thu, Jan 29, 2015 at 2:07 PM, Mario M. Westphal wrote: > Most database damaged errors encountered over time could be pinned to > power failures, disk or *network problems*. > > Network problems? I might have missed a good chunk of this thread, but, this begs to be

Re: [sqlite] "database disk image is malformed" error occurs more

2015-01-30 Thread Stephen Chrzanowski
On Fri, Jan 30, 2015 at 8:07 AM, Mario M. Westphal wrote: > > When a user encounters the problem he/she restores the last working > backup. I have a few users who faced this problem more than once. Here I > always assumed some hardware glirch, a shaky USB connection, disk

Re: [sqlite] Possible to get table size (in bytes)?

2015-02-04 Thread Stephen Chrzanowski
Wrong answer. He's asking how many bytes a table takes up within the database. That would depend on a whole lot of factors including what is stored, the number of fields, etc. Is there SQL code to get requested result? Short answer, No. Not easily. Long answer, a table isn't a certain number

Re: [sqlite] SQL Help

2015-02-09 Thread Stephen Chrzanowski
Maybe with a query or two extra, you can determine a temp table, then build on that. Do an initial distinct look up on the primary fields you want as the fields in your temp table and create it, then do the required queries to get the raw data into the temp table, then do the finalized query to

Re: [sqlite] Messages posted on Nabble not getting to list

2016-05-26 Thread Stephen Chrzanowski
Times have changed, because people get 'bright ideas' that just don't work. I don't want to look at 30 different forums that might claim "The official forum of SQLite" or whatever. Also, I don't have to worry about security different forum services offer. Everything comes right to my email, and

Re: [sqlite] Messages posted on Nabble not getting to list

2016-05-27 Thread Stephen Chrzanowski
I'm a big time die hard fan of "text only". I'm an information consumer, and not someone who likes being distracted by meaningless visual effects. My Linux consoles are all (mostly) white on black, my 5-monitor Windows desktop looks like Windows 2000, and my 3-monitor work machine follows that

[sqlite] sqlite3 (or sqlite4) performance on NFS

2015-04-11 Thread Stephen Chrzanowski
>From what I understand; - Read-Only data - Data doesn't change frequently - Central repository for data - Network latency causing issues My two cents on this is to keep a database revision ID kicking around and do a SQLite backup of the remote data to a local storage medium. At application

[sqlite] json_* functions in sqlite

2015-04-22 Thread Stephen Chrzanowski
I'm no where near the level of an overlord, except maybe to the wifes dog. I'm in a debate mood, so why not? I'm open to the firing squad today. :] IMHO, there are four (I initially started with two) problems with this request in making it part of the core dealings Dr Hipp provides us all. I

[sqlite] design problem involving trigger

2015-08-21 Thread Stephen Chrzanowski
Another option would be to not use NULL but use an empty string. On Fri, Aug 21, 2015 at 9:17 AM, R.Smith wrote: > > > On 2015-08-21 04:47 AM, Will Parsons wrote: > >> I'm working on a program that involves a catalogue of books. Part of >> the database schema looks like this: >> >> create

[sqlite] order by not working in combination with random()

2015-08-25 Thread Stephen Chrzanowski
Somewhat of a devils advocate here, but I'm not sure one can order based on JUST data. Take the DATE function, for example. If, by your words, ORDER BY should only act on the data, consider this kind of query: select ItemName,SoldDate from SoldItemDetails order by SoldDate

[sqlite] order by not working in combination with random()

2015-08-26 Thread Stephen Chrzanowski
On Wed, Aug 26, 2015 at 3:00 AM, J Decker wrote: > but, your order by is the only place that uses the date function... it > would have to be reversed as > elect ItemName,SoldDate, date('now','-1 month') as z from > SoldItemDetails order by > SoldDate > which I would think would evalutate to a

[sqlite] Why sqlite show qualified column names when selecting from views ?

2015-08-26 Thread Stephen Chrzanowski
I'm sure either #1 or #3 happens already. I know I've been nagged about ambiguous field names, but I also know that somewhere I've seen _1 or _2 be appended to field names (That might be just the SQL IDE I use doing that). I can't get on board with #2 because most wrappers only look at the first

[sqlite] Insertion into the large DB drastically slows down at some point

2015-08-27 Thread Stephen Chrzanowski
I'm with Simon. If you're writing 5 million rows to memory, the OS has to move unused memory out to disk. I'd say write the data to disk (SSD if you can) and check if the data written out is at a consistent speed. On Thu, Aug 27, 2015 at 10:29 PM, Simon Slavin wrote: > > On 28 Aug 2015, at

[sqlite] bug when columns are missing in embedded subselect

2015-12-15 Thread Stephen Chrzanowski
I work for a flight planning software house, so I had to take a double-look at this. Competition, eh? ;) On Tue, Dec 15, 2015 at 4:14 PM, Richard Hipp wrote: > > > Interesting timing: I was monitoring an inbound flight on flightaware > when this issue report arrived in my inbox. :-) > > -- >

[sqlite] whish list for 2016

2015-12-24 Thread Stephen Chrzanowski
Two parter; *Part 1;* One thing I would suggest, if you're looking to add and delete columns dynamically is not to worry about the order of the columns in the database, but, have a second table hanging around that remembers the specified order the user wants to see the columns in. Doing your

[sqlite] test for bounce detection

2015-02-14 Thread Stephen Chrzanowski
GMail has constantly and randomly flagged different messages from this mailing list as spam. I set a specific mail rule to force the mail to NOT be flagged as spam. The nice thing is, GMail also tells me when the message SHOULD have been put into spam but because of my mail rule, the message

[sqlite] Complex insert query to normalised database

2015-02-18 Thread Stephen Chrzanowski
AFAIK, you can't do 'nested insert', or, insert to multiple tables in one call. Not from a single command line, or, from a view. You're pretty much stuck with updating one table at a time. It would be nice, however, problems can come up with a many-to-many situation where the engine isn't sure

[sqlite] Appropriate Uses For SQLite

2015-02-19 Thread Stephen Chrzanowski
Dr Hipp; If you're doing a PR stunt, you should take a lot of these emails and put them on a "What users say" page and link them to the email thread (If it is accessible by the public -- I get this list in my email). That way you'd get real comments about the stability of SQLite against real

[sqlite] List duplication

2015-02-27 Thread Stephen Chrzanowski
I've not been having problems with duplicates, but I have been randomly having commoners end up in my spam bucket for one reason or another over the last 48 hours or so. I've specifically set a filter in GMail that if anything comes in for this mailing list to NOT go to spam, yet On Fri, Feb

[sqlite] Best way to handle time slicing of SQL data.

2015-06-02 Thread Stephen Chrzanowski
On Tue, Jun 2, 2015 at 7:34 AM, Richard Warburton < richard at skagerraksoftware.com> wrote: > 1) Do I need UID? I'm currently using it to ensure a unique Id for when > the user is creating a new entry. This however means two operations, an > Insert, then an Update to set the record's Id to

[sqlite] SourceForge seems to have grabbed the "sqlite" project there

2015-06-03 Thread Stephen Chrzanowski
I can see why they're doing it as well. Some people have different preferences to grab the repo. I have a script running on one of my linux boxes that checks periodically (Once a week? Once a month? I can't remember) for all links on the SQLite download page. If I don't have the URL or file

[sqlite] User-defined types

2015-06-05 Thread Stephen Chrzanowski
First, who said that you had to keep all 6 sets of languages in your head at once? I've never been told that, and I've been doing software development since I was 8, taken several training courses in elementary, high school, college, and while employed by three different companies (At different

[sqlite] Mozilla wiki 'avoid SQLite'

2015-06-14 Thread Stephen Chrzanowski
Most of that looks to be more like common sense things rather than SQLite specific, so why they're calling out SQLite, I've no idea. Also, this doc was last modified more than a year ago. Stuff has changed both within FF as well as SQlite since then. I've just deleted the blog I was going to

[sqlite] Mozilla wiki 'avoid SQLite'

2015-06-14 Thread Stephen Chrzanowski
within is absolutely correct IMO, is that they're targeting mostly the wrong foundation. I know that it is a page of "Things to watch for" but targeting SQLite itself.. well.. I feel that the author is looking through the wrong looking glass. On Sun, Jun 14, 2015 at 11:57 AM, Scott Robi

[sqlite] Mozilla wiki 'avoid SQLite'

2015-06-14 Thread Stephen Chrzanowski
I as well disagree that "the higher language, the better". The bloat .NET adds to the application size for both processing, memory and drive usage is astronomical. Its like going back to Visual Basic and including the interpreter in your "compiled" application with your source code basically

[sqlite] Tables and Columns of Database of Whatsapp

2015-06-30 Thread Stephen Chrzanowski
There are a few posts in this mailing list that have people putting up public domain database structures, with data, for different things to track. Google search also has come up with a few out there as well, but, you really got to nail down the query as just searching for "Public domain

[sqlite] Having issue working with SQLite & Windows Phone 8.1!

2015-03-22 Thread Stephen Chrzanowski
Do you have a working database object that has its hands on the physical database? At application initilization when opening the database, perform a simple query against this table, or another, and verify that you are indeed pulling from the CORRECT database. Do a search on the local file system

[sqlite] What is wrong with this simple SQL?

2015-03-22 Thread Stephen Chrzanowski
I think the confusion comes down to where the subslect only refers to exactly one table. That single query has nothing to do with any other table. There isn't a join, there isn't a reference to any other table, so the fuzzy question is why would an ambiguous error come up if there is exactly

[sqlite] Multiple instances of the same program accessing the same db file

2015-05-04 Thread Stephen Chrzanowski
TL;DR Get SQLite away from networks entirely when you start throwing multiple machines at the problem. Your last paragraph is probably the best bet if you're not willing to get into a real client/server setup, and yes, you're going to run into aggravated users, but, then, which is more important

[sqlite] What software is deployed more than SQLite?

2015-05-05 Thread Stephen Chrzanowski
No... Skype doesn't come with ALL versions of Win 7 or later. I've purchased three copies of 8.1 since Dec and none of them come with Skype. Two Notebooks with the OS installed as well as an OEM I've downloaded from MSoft. I'm not saying that MSoft doesn't have an application or two that uses

[sqlite] SQLite queries

2015-05-07 Thread Stephen Chrzanowski
Although I can understand the sarcasm you're sending out, a client/server infrastructure would be an interesting task for SQLite to do, but to answer the OP, no, SQLite isn't inherently designed to be a client/server. But, think of how torrents work. Everyone is a server, everyone is a client.

[sqlite] SQLite queries

2015-05-07 Thread Stephen Chrzanowski
On Thu, May 7, 2015 at 12:25 PM, John McKown wrote: > On Thu, May 7, 2015 at 10:01 AM, Stephen Chrzanowski > wrote: > > > Although I can understand the sarcasm you're sending out, a client/server > > infrastructure would be an interesting task for SQLite to do, but t

[sqlite] AUTOINC vs. UUIDs

2015-05-22 Thread Stephen Chrzanowski
I've been watching this thread from the beginning with great interest, and I still don't see the difference between using a UUID or an auto-inc integer as a PK at the very raw, basic level. The database will only see them as a string of bits or bytes and handle accordingly. IMO, using UUID is an

[sqlite] AUTOINC vs. UUIDs

2015-05-22 Thread Stephen Chrzanowski
{{I just got a bounced message.. Reposting}} I've been watching this thread from the beginning with great interest, and I still don't see the difference between using a UUID or an auto-inc integer as a PK at the very raw, basic level. The database will only see them as a string of bits or bytes

Re: [sqlite] Disparity between query time in SQLite Administrator and Flash Pro

2013-11-06 Thread Stephen Chrzanowski
The 15 seconds "smells" like a time out. I've found in the past where if the database being accessed is in read only mode (Either by filesystem permissions, or just the plain old READ ONLY file attribute) SQLite pauses. I can dupe this with using any utility, be it the SQLite CLI or PHP or

Re: [sqlite] Disparity between query time in SQLite Administrator and Flash Pro

2013-11-07 Thread Stephen Chrzanowski
SQLite Expert allows you to use any SQLite DLL. Its free for non-commercial use, but even that said, its an awesome package to buy, life time upgrades, and seemingly always being updated. http://www.sqliteexpert.com/ On Wed, Nov 6, 2013 at 7:30 PM, SongbookDB

Re: [sqlite] Feature request: Better support for database file moving

2013-11-11 Thread Stephen Chrzanowski
Two questions; - Outside of SQLite, how would you handle "better checking" if a file randomly moves stuff around when a transaction is happening? - Why would a user move a database file while the program is in use? You can plan for every kind of problem, but you can't prevent every problem.

Re: [sqlite] executing queries on normalized database

2013-11-11 Thread Stephen Chrzanowski
I agree with Simon, but I also twist things up a bit. Databases are just data storage containers, at the most low level, raw definition. A text file could be a "database" for all intents and purposes. There should be things in there to protect the data within, but there also needs to be some

Re: [sqlite] Feature request: Better support for database file moving

2013-11-12 Thread Stephen Chrzanowski
unclear if the OP wants to do this or expects to be > > able to move the whole lot while the app is running, which > > is not something I'd expect to be able to do. > > Sorry for the confusion. I'm talking about moving individual documents. > See above replies to Stephan and Simon. >

Re: [sqlite] Feature request: Better support for database file moving

2013-11-12 Thread Stephen Chrzanowski
**kind of off topic** @Tim> I'm no where near in thinking that it should be SOP. I'm somewhat appreciative of not being allowed to change the "file containers" visual representation a file while something has its fingers on it. Just as I tell my kids, "if you make the mess, you clean it up.

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Stephen Chrzanowski
I'm kind of confused with this new 'toy'... I read the doc linked, but kind of scratching my head. I FULLY acknowledge this is an optional parameter. So you create a table with the WITHOUT ROWID optional command, and you'll lose the effect of sqlite3_last_insert_rowid() after an insert, which

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Stephen Chrzanowski
On Fri, Nov 15, 2013 at 10:15 AM, RSmith wrote: > > Now disregarding all the above - The very only reason you would use the > WITHOUT ROWID optimization on any table is precisely because you are NOT > using an integer primary key but because you are adding proper text values

[sqlite] Trouble with Journal_Mode Pragma

2013-11-16 Thread Stephen Chrzanowski
I'm not sure why this code is breaking: procedure SaveDatabaseTo(fName:string); var TempDB:tsqlitedatabase; begin TempDB:=TSQLiteDatabase.Create(fName); TempDB.ExecSQL('PRAGMA journal_mode = OFF'); db.Backup(TempDB); tempdb.free; end; It fails at the PRAGMA statement. In the CLI, the

Re: [sqlite] SQLite server/file-locking scenario

2013-11-17 Thread Stephen Chrzanowski
This is gonna be a long one, but I added (hopefully) some humor to make it a bit better of a read. @OP Just so I'm clear, you're pushing SQL statements from clients to your server, but then pushing raw data from the server to the client? Myself, I'd never push a "Work In Progress" database, or a

Re: [sqlite] Trouble with Journal_Mode Pragma

2013-11-17 Thread Stephen Chrzanowski
I think I found out why I'm getting a raised error and this will be something I'll have to remember later on dealing with PRAGMAs. There are two ways to get things done with this unit. There is GetTable which expects results back, and ExecSQL which expects exactly zero rows back. In the CLI and

[sqlite] ETA on version 4

2013-11-25 Thread Stephen Chrzanowski
I realize that it is still a Work In Progress, and I realize I can download the code and compile for my code. But I'm kind of curious as to what the remaining life span of version 3 is looking to be. When do you think (In resolution of months and/or years) it will be out? UNKNOWN is acceptable

Re: [sqlite] ETA on version 4

2013-11-25 Thread Stephen Chrzanowski
Thanks all. I'll keep on chugging with 3 then. :] Richard, "Forever" is a long time. Did you ever say that for version 2? ;) On Mon, Nov 25, 2013 at 3:27 PM, Richard Hipp <d...@sqlite.org> wrote: > On Mon, Nov 25, 2013 at 3:20 PM, Stephen Chrzanowski <pontia...@gmail

Re: [sqlite] Big number of tables

2013-11-29 Thread Stephen Chrzanowski
As others have suggested, transactions, create a single file and just copy, or throw it in memory (And as an extra thought to creating it in memory is once created, throw it to disk via the Backup API then --if needed-- work off that). Another option, but probably not the best idea, is each time

Re: [sqlite] Bug in sqlite.exe?

2013-11-30 Thread Stephen Chrzanowski
Try using SQLite Expert (For your short term use, use the free version) and see if you can pull up the DB. You can select which DLL you want to use and see if its a library issue or if its something to do with the database, or whatever else. I have a bunch of DLLs in my VCS that I can extract

Re: [sqlite] Concrete example of corruption

2013-12-05 Thread Stephen Chrzanowski
@Warren> It was mentioned in another thread, of this exact subject (I think this is the third thread?) that the package/sandbox idea won't work due to certain constraints the OS puts on the file. I don't recall what the reason was, exactly, as I've never used a Mac for any kind of considerable

[sqlite] Best method for a multi-thread application

2014-01-13 Thread Stephen Chrzanowski
Please note, if you'd like to discuss this program as an entity outside this thread, feel free to contact me directly. I think my email address is exposed in this thread. I'm writing a new program** to keep track and manage my MP3s and will allow me to sync different sets of MP3s to different

Re: [sqlite] Boolean and DataReader

2014-01-30 Thread Stephen Chrzanowski
Just to make it crystal clear, to a developer actually calling the SQLite functions (I'm talking about [ PrepareSQL_v2 ] and such, not [ select * from table ]), SQLite is TYPELESS which means there is no data type that is kept track of for any field. This means that even if you define a column as

Re: [sqlite] How to Troubleshoot Disk I/O Error

2014-01-30 Thread Stephen Chrzanowski
What exact Disk I/O error are you getting? There are many different types. - Permissions to read are denied - Invalid characters in the filename - Corrupted database etc On Thu, Jan 30, 2014 at 10:19 AM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) < aolor...@bloomberg.net> wrote: > Hi, > > We

Re: [sqlite] Keeping Track of Records of IDs in one table. Possible?

2014-01-30 Thread Stephen Chrzanowski
Untested and only from the SQL compiler in my brain -- This compiler is known to have a few bugs -- It may also be too late to go use this, so this might be something to look at if you plan on upgrading; This is the first way I'd do it; create table Projects (ProjectID INTEGER PRIMARY KEY

Re: [sqlite] Boolean and DataReader

2014-01-30 Thread Stephen Chrzanowski
I don't use .NET anything, so I can't speak on it. However, with my wrapper in Delphi, if I store a value as 1 or '1', returning a .AsInteger will succeed. The wrapper is smart enough to decide if it is actually a number. I don't recall if it'll throw an exception if the value in the database

Re: [sqlite] Keeping Track of Records of IDs in one table. Possible?

2014-01-30 Thread Stephen Chrzanowski
y to capture the > understanding of some of the syntax. But this is great! thanks. > > Stephen Chrzanowski" wrote... > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fine tuning table indexes

2014-01-31 Thread Stephen Chrzanowski
On Fri, Jan 31, 2014 at 12:05 PM, Simon Slavin wrote: > > However, you also mention that your app generates its own commands. > Unless you can predict things about these commands ("90% of the time users > are going to want to sort by date") you're not in a good place to

Re: [sqlite] Fine tuning table indexes

2014-02-01 Thread Stephen Chrzanowski
;wrote: > On Fri, Jan 31, 2014 at 8:49 PM, Stephen Chrzanowski <pontia...@gmail.com> > wrote: > > > I've not done anything to 'generate' a SQL statement, but I can see the > use > > of this just as a debugging tool to figure out why something is taking

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Stephen Chrzanowski
I was just going to suggest that John. Short of hitting CTRL-C to break out of the program, the user may have to "double-quit" if no file path has been given to be saved to, just for confirmation. > .q !!Warning - In-Memory Database not saved. Quit again to exit without saving > .q

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread Stephen Chrzanowski
Personally, I don't buy that DropBox is the culprit as I've done this kind of thing a few times in a few applications of my own, however, I'm the single user that works on that single account, and any app that uses DB is usually under development and "closed" on any other geographical site.

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-11 Thread Stephen Chrzanowski
Lets not throw honey at the problem when a bear is around. Some of the things I've seen in this thread just makes it sound like the kitchen sink should be included in this application. I don't like the idea of letting the software decide what should be done based on a configuration file. Linux

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

2014-02-12 Thread Stephen Chrzanowski
Three thoughts; *First*, buy a bulk amount of cheap, inexpensive USB keys and start throwing your data at the key instead of your OS's card. I'm not 100% clear on how a USB key handles itself as far as writing to certain parts of its memory, but you could partition off chunks of space and just

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

2014-02-12 Thread Stephen Chrzanowski
There are a few reasons for the two-database method would be useful versus a single database connection, depending on the volume of data in the pot. 1> Having a single hourly database will keep the database size minimal. ~8000 entries holding just temperature data, it'll be small so I can't see a

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

2014-02-12 Thread Stephen Chrzanowski
If you write your information to the cheap* USB key instead of the SD card, from a mile-high view, you're looking at a bad data disk instead of a bad OS disk. You could backup daily your USB version of the database to the SD card, or to a network drive (If available) so then you're only writing

Re: [sqlite] help needed for major SQLite problem

2014-02-12 Thread Stephen Chrzanowski
Information on how to open SQLite files: http://www.sqlite.org/c3ref/open.html How the locking mechanisms work: http://sqlite.org/lockingv3.html Specifically, it'll depend on the language or wrapper you're using to access the database. In my case (Delphi) there is an option in the open function

Re: [sqlite] SQLite destroys civilization.

2014-03-02 Thread Stephen Chrzanowski
Its gotta be great to see your code end up in a TV show and have an actor say "There's you're problem" and you get to say "Not in my code!". That would have been epic to be sitting there for that particular event as a bystander. heh On Sun, Mar 2, 2014 at 6:39 PM, Darren Duncan

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

2014-03-09 Thread Stephen Chrzanowski
Apologies for the interruption and sort of off topic, but, is .timer part of the CLI only or is it part of the SQL language? Can I get the result of a timer from a call, or do I have to put a wrapper on my wrapper? On Sun, Mar 9, 2014 at 8:17 PM, Keith Medcalf wrote: > >

Re: [sqlite] Windows user app to display input form + reporting?

2014-03-11 Thread Stephen Chrzanowski
SQLite Expert has a free version that can be used. Certain UI features are missing, but, it does allow for excel like table editing. Its also MUCH cheaper than anything Microsoft has put out, even with their "dealer discounts" and "oem" sales with proper hardware purchases. On Tue, Mar 11,

Re: [sqlite] Remote access to SQLite in Linux from Windows.

2014-04-20 Thread Stephen Chrzanowski
Short of writing a server type application that listens for incoming communication, I'm not aware of anything of the sort. SQLite is an embedded library which compiles with your code, or, with references to external libraries installed on your local machine. SQLite doesn't behave well with ANY

  1   2   3   4   5   6   >