Re: [sqlite] proposal for write-lock on "commit" rather than "begin transaction"

2019-10-25 Thread Brannon King
> > Two users – members of staff – enter data. Each user enters a new > invoice. One of these entries gets rolled back. What should their > software do ? Or should it just return an error message to the user ? > Multi-user data entry is not a part of my intended use case. I think other

[sqlite] proposal for write-lock on "commit" rather than "begin transaction"

2019-10-25 Thread Brannon King
This is a request for a small change to the handling of multiple connections. I think it would significantly enhance the usefulness there via allowing multiple "views" of the data. Consider that I have two simultaneous connections to one file, named Con1 and Con2. They could be in one process or

Re: [sqlite] rationale for private archives of the SQLite mailing list and suppressed reply-to

2019-10-11 Thread Brannon King
I agree that Mailman is archaic. I worry about the security on it. I don't enjoy using 3rd-party mirrors for searching it. I'd like to propose that we upgrade to something more modern and secure like Sympa or mlmmj, or even a more drastic system upgrade to something like Redmine -- a project

[sqlite] SELECT uses index with SUBSTR but UPDATE doesn't

2019-10-08 Thread Brannon King
I have this query: UPDATE nodes SET parent = ? WHERE SUBSTR(name, 0, ?) = ? EXPLAIN QUERY PLAN tells me that it is going to do a table scan. At the same time, the query plan for this: SELECT * FROM nodes WHERE SUBSTR(name, 0, ?) = ? tells me that it can and will use the (primary key) index on the

Re: [sqlite] performance difficulty across multiple queries

2012-05-31 Thread Brannon King
>I recommend you execute the SQL command ANALYZE then try various of those SELECTs again. This can cause the SQLite optimizer to make different choices about which indexes to use. Thanks for the suggestion. I am familiar with the Analyze command and have been using it. However, it doesn't seem

[sqlite] performance difficulty across multiple queries

2012-05-31 Thread Brannon King
I've got this (simplified) table schema: CREATE TABLE [Services] ([Id] INTEGER PRIMARY KEY, [AssetId] INTEGER NULL, [Name] TEXT NOT NULL); CREATE TABLE [Telemetry] ([Id] INTEGER PRIMARY KEY, [ServiceId] INTEGER NULL, [Name] TEXT NOT NULL) CREATE TABLE [Events] ([Id] INTEGER PRIMARY

RE: [sqlite] opening a database in command prompt in Windows

2006-08-21 Thread Brannon King
Dragging a database onto the sqlite.exe in explorer works. > I downloaded the command line utility, and it is an exe file > that automatically opens the sqlite prompt. Given this, how > am I supposed to open or create databases? The only way I'm > aware so far is that you have to specify the

RE: [sqlite] Return value of sqlite3_last_insert_rowid()?

2006-08-02 Thread Brannon King
a "normal C long" on 64 bit linux would be the same data type as sqlite_int64. On a 32bit system, just cast it (assuming you know that you'll never have more than 2 billion rows). > What I'm not clear about is that the documentation says that > rowid is an alias for the primary key column of

RE: [sqlite] memory leak

2006-08-02 Thread Brannon King
One quick question, is that with memory management enabled in the sqlite compile or not? I've done some extensive memory checking with memory management disabled and never found a leak. However, with memory management enabled, all bets are off. > I believe the memory leak exists. The following is

RE: RE: AW: AW: [sqlite] New JDBC driver for SQLite

2006-07-31 Thread Brannon King
> Thanks for the info. Points 2, 4 and 5 are covered by the > Makefile (DB.h is generated with javah), but I'll patch the > project for the variable declarations and the cast. Though I > have a feeling the cast is unncessesary, did VC throw an > error or warning for that? It was just a

RE: AW: AW: [sqlite] New JDBC driver for SQLite

2006-07-31 Thread Brannon King
To compile the binary with VC71, I had to 1. move a dozen variable declarations to the top of the function 2. download the DB.h file separately from the build tree 3. change the jstrlen to end with "return (int)(s - str) - suppChars" 4. change my sqlite3 lib build to #define

RE: [sqlite] Enabling SQLITE_ENABLE_MEMORY_MANAGEMENT on Visual Studio.Net

2006-07-27 Thread Brannon King
You'll have to change that in the makefile before building the "target_source" code, which means you'll need Linux or a similar build environment on Windows. I don't think you want to do that anyway. MS's memory handlers don't like that at all. For proof (once you get it enabled) put this into

RE: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison

2006-07-20 Thread Brannon King
The VC6 compiler is from 1998. The VC 7.1 or 8.0 compilers produce better compilations. I'm certain any Borland or GNU compiler of the past 3 years would also produce better assembly than VC6. And if somebody has their hands on a PathScale or Intel compiler, please post some benchmarks! > I

RE: [sqlite] count(*)

2006-07-19 Thread Brannon King
>> select rowid from table limit 1 offset -1; > Two ways to do this: > >SELECT rowid FROM table ORDER BY rowid DESC LIMIT 1; >SELECT max(rowid) FROM table; Yes, but neither one of those would be as fast as this query, true? SELECT rowid FROM table LIMIT 1 I guess I was thinking to

Re: [sqlite] count(*)

2006-07-18 Thread Brannon King
I know that this list has been over the issues of using ROWID to get the count. Nevertheless, I was thinking, if you never do any deletes the last ROWID should contain the count. It's too bad you can't do an offset of negative one so that it would start at the back. That should be darn fast.

RE: [sqlite] Re: I need help making this code run faster

2006-07-18 Thread Brannon King
Thanks, Igor, you've inspired and saved me yet again. The subqueries you had used for the x/yEnd did not work, but the rest did and I have that maxim information beforehand anyway. Here's how it shook down: select cast(cast((xStart+xEnd) as double)/2/15518.5 as integer) cellX,

RE: [sqlite] I need help making this code run faster

2006-07-18 Thread Brannon King
I can see no difference in my time measurements in changing the "order by desc limit 1" to "max". > >I'm wondering if the following query can be done as a single query > >rather than running it in a (nested) loop. > > > >Suppose a database with five columns; xStart, yStart, xEnd, yEnd, >

[sqlite] I need help making this code run faster

2006-07-18 Thread Brannon King
peed this up, please mention it. __________ Brannon King ¯¯

[sqlite] timer in shell.c part II

2006-07-18 Thread Brannon King
SSE2 and omit frame pointers. __ Brannon King ¯¯

[sqlite] timer in shell.c

2006-07-17 Thread Brannon King
le? Thanks. ______ Brannon King ¯¯

[sqlite] fast Java solution?

2006-07-17 Thread Brannon King
rhead? I suppose I'll dig in and make a custom JNI interface so that most of my code is done in C, but just thought I'd ask around first... Thanks for your time. The other Java wrappers posted seem to wrap too much or too little or not be compatible with version 3. _ Br

[sqlite] icons and custom builds

2006-07-01 Thread Brannon King
Is there a place I can post some icon files made from the logo on the main sqlite.org page? And where would I post unofficial builds of sqlite3.exe, like the one I did with current CVS today using VC71 and embedding the icon?

Re: [sqlite] SQLite 3.3.6 - possibly memory leak

2006-06-12 Thread Brannon King
I've seen a few issues myself, but they all appeared to be related to the memory management thing. If you're using VC, try putting this into your code: #define CRTDBG_MAP_ALLOC #include #include // and then in your main function: _CrtSetDbgFlag ( _CRTDBG_ALLOC_MEM_DF | _CRTDBG_LEAK_CHECK_DF

RE: [sqlite] Problems with multiple threads?

2006-06-07 Thread Brannon King
For me, I have a bunch of threads writing to the database. That is the only part I do multithreaded. (All my read queries are handled after all the data is written.) I just use the scoped_lock operator from the Boost library at the top of my function that does the bind and step calls. I pass a

[sqlite] threading vs no threading compilation performance

2006-05-30 Thread Brannon King
transaction running at any given time. __ Brannon King ¯¯

RE: [sqlite] Re: seeking answers for a few questions about indexes

2006-05-26 Thread Brannon King
> > EXPLAIN QUERY PLAN SELECT * FROM vals2d WHERE x = 1 OR y = 1; > > select * from vals2d where x=1 > union all > select * from vals2d where y=1 Super! You've greatly helped me yet again. My query really looked like this: SELECT x,y,val FROM vals2d WHERE x = 1 OR y = 1 ORDER BY val DESC

[sqlite] seeking answers for a few questions about indexes

2006-05-26 Thread Brannon King
or your time. __________ Brannon King ¯¯

RE: [sqlite] can you speed this query up?

2006-05-23 Thread Brannon King
Thank you for taking the time to sort out my query! (The meat is at the bottom.) > SELECT > r.qi, > r.ri, > r.drl, > r.score > FROM > results_1 r > INNER JOIN bounds b ON > r.qis = b.bqis AND r.ris = b.bris > WHERE >

Re: [sqlite] can you speed this query up?

2006-05-22 Thread Brannon King
select qi, ri, drl, max(score), min(score) from ... What values of qi, ri, and drl would you want this query to return? What you have to do is: SELECT qi, ri, drl, score FROM ... WHERE score=(SELECT max(score) FROM ...) Thank you for the instruction, although the other query

[sqlite] can you speed this query up?

2006-05-22 Thread Brannon King
0| 65|Close|2|0| 66|AggFinal|3|1|max(1) 67|MemLoad|0|0| 68|MemLoad|1|0| 69|MemLoad|2|0| 70|MemLoad|3|0| 71|Callback|4|0| 72|Halt|0|0| 73|Transaction|0|0| 74|VerifyCookie|0|4| 75|Transaction|1|0| 76|VerifyCookie|1|3| 77|Goto|0|6| 78|Noop|0|0| Thanks again for any help. __ Brannon King ¯¯

[sqlite] reset function speed

2006-05-22 Thread Brannon King
is allocated in blocks, but deallocated one piece at a time. __ Brannon King ¯¯

[sqlite] scary warnings

2006-05-22 Thread Brannon King
a analyze.c alter.c Generating Code... Creating library... sqlite - 0 error(s), 199 warning(s) __ Brannon King ¯¯

RE: [sqlite] create unique index quickly

2006-05-22 Thread Brannon King
Thank you for answering the question! Like I said in my other post, the index creation is about 20% faster than the unique index creation in my latest tests of an index of two INTEGERs. 20% out of several hours is significant. So index names are unique for the whole database? I was thinking they

RE: [sqlite] create unique index quickly

2006-05-22 Thread Brannon King
> I am simply curious. This sounds like an amazing > engineering challenge. If it is not a secret, can you > describe what this data represents and how it will be used? Genomics. Look up "Smith-Waterman" or "Needleman-Wunsch-Sellers" on the web. > What is the ultimate source of this

RE: [sqlite] I need help understanding fake columns

2006-05-22 Thread Brannon King
> If I understand it right you'll definitely need a correlated subquery. > You need one query to calculate the max() of the 257(0-256?) values. > There must be some way to define which values should be used > in that calculation for a specific piece (the distance). Is > the distance geometric

RE: [sqlite] create unique index quickly

2006-05-22 Thread Brannon King
> _Personally_ I think, this sounds like a task not quite > fitting in sqlites (probably any 'standard' databases) realm. > This is a bit off-topic in this group, but because you > mention sub-boxes - did you ever look into more specialized > file-formats like HDF5: > >

Re: [sqlite] I need help understanding fake columns

2006-05-21 Thread Brannon King
Jay Sprenkle wrote: score(x,y) = max(score(x+n,y+n)-n*penalty) where n < drc(x+n,y+n) < 256 So at some 'sample' you have the score and drc, but not the x,y value? The x,y values can be interpolated using some algebra and the surrounding samples? Uh, no. Sorry my other emails were unclear. At

Re: [sqlite] create unique index quickly

2006-05-20 Thread Brannon King
John Stanton wrote: You don't seem to need a data manipulation system like Sqlite, more a form of high volume storage. Do you really need elaborate SQL, journalling, ROLLBACK and assured disk storage? Di you consider some form of hashed storage, perhaps linear hashing, to build a compact

Re: [sqlite] create unique index quickly

2006-05-20 Thread Brannon King
For your mail i think you have everything planned, but just for curiosity, how do you plan to pass data from hardware (800 MB/s + Database data) to a raid?? A GigEthernet has 1000 Mb/s (so, 128 MB/s), a PCI-X based solution has 1.3 to 2.6 Gb/s (so 150-300 MB/s), and similar for SATA and

Re: [sqlite] Index syntax for attached database

2006-05-20 Thread Brannon King
The documentation says to put the database name on the front of the index name, not the table name when using the create index command. I thought it was weird myself. Micha Bieber wrote: I'm receiving sql errors like the following "no such table: main.phs_matrices_1" when trying to create

Re: [sqlite] Fwd: C++ callback with class fuctions

2006-05-20 Thread Brannon King
Declare it static in the class declaration, not at the function definition. Esteban Zeller wrote: El Sábado 20 Mayo 2006 21:40, Brannon King escribió: Is it declared as a static function? Something like "static int bd::analisis_ultimo(void* arg)" ? I assume it would need to be s

Re: [sqlite] Fwd: C++ callback with class fuctions

2006-05-20 Thread Brannon King
Is it declared as a static function? Something like "static int bd::analisis_ultimo(void* arg)" ? I assume it would need to be static just like all the other thread function parameters or callback parameters in various APIs. Esteban Zeller wrote: I'v got the next problem: string cola =

Re: [sqlite] I need help understanding fake columns

2006-05-20 Thread Brannon King
Dennis Cote wrote: Where did you get the idea there are "fake" columns? "SELECT 200" returns 200. I'd call that a fake column. What is the proper name for it? What I was picturing was something like this: "SELECT (BETWEEN 200 AND 500) as a, a+2" and then get 300 outputs. That would be

Re: [sqlite] create unique index quickly

2006-05-20 Thread Brannon King
The benefits I'm trying to get out of sqlite are the data queries. I collect a large, sparse 2D array from hardware. The hardware device is giving me a few GB of data data at 200MB/s. Future hardware versions will be four times that fast and give me terabytes of data. After I have the data, I

[sqlite] I need help understanding fake columns

2006-05-19 Thread Brannon King
t line to include '(select 300) as a'. That still didn't let me do what I really wanted to do; use 'a' in the max function. What's the right way to do that? And how do I simply return a range of numbers with a select statement? Thanks. __ Brannon King ¯¯

RE: [sqlite] Re: help with simple query, please

2006-05-19 Thread Brannon King
It works like a champ. Thanks. > > Suppose I have two tables, A and B. A has two columns: score, > > location. B has two columns leftLocation, rightLocation. > > > > I need the maximum score located outside all entries in > table B. B is > > a small table, say less than 100 entries. > >

[sqlite] help with simple query, please

2006-05-18 Thread Brannon King
Suppose I have two tables, A and B. A has two columns: score, location. B has two columns leftLocation, rightLocation. I need the maximum score located outside all entries in table B. B is a small table, say less than 100 entries. I tried this: SELECT max(score),location FROM A, B GROUP BY

[sqlite] create unique index quickly

2006-05-18 Thread Brannon King
already declared. Is there some way I can create that unique index without the overhead of uniqueness checking? I know all my triples are unique when used together: I generated them that way. Or is there some other way I can create that index faster? Thanks. __ Brannon

[sqlite] opcode count for sqlite3_progress_handler

2006-05-18 Thread Brannon King
nks again, __ Brannon King ¯¯

RE: [sqlite] add primary key after inserts?

2006-05-18 Thread Brannon King
> I'm not sure what you are getting at here. Copy memory from > where to where? The database is much more than an array of > record structures that you can copy data into. > > Dennis Cote I've been studying the profile and can see my thinking was wrong. I was thinking we would just log an

RE: [sqlite] add primary key after inserts?

2006-05-18 Thread Brannon King
So I dropped the "not null" and it gave a ~2% increase in overall speed. It's not much, but may count for something. It's a little strange because that is just the opposite of MySQL. In MySQL, it shrinks the database size and speeds up the access by declaring a column not null. I am using the

RE: [sqlite] add primary key after inserts?

2006-05-18 Thread Brannon King
>Also, do a large number of inserts within a transaction. (1000-100,000 or so, not a billion ;) ) I don't see how increasing the number of inserts per transaction changes the speed at which the sqlite3_step function executes when it is called on an insert in the middle of the transaction. I'll

[sqlite] add primary key after inserts?

2006-05-17 Thread Brannon King
As I understand SQL, "alter table blah add primary key (blah1, blah2)" should be how you do it. The sqlite documentation seems to say otherwise. Actually, I cannot figure out from the documentation how to add a primary key after the table is created and data is entered. How is it done? Or is

RE: [sqlite] Make for windows

2006-05-17 Thread Brannon King
Thanks. That command created the files; however, grep sqlite3_sleep ./tsrc/* reports: sqlite3.h:int sqlite3_sleep(int); and that's all. In other words, it's declared but never defined. > So I downloaded the latest CVS files on my Linux box. What do I run to > generate the c files I need for

[sqlite] Make for windows

2006-05-17 Thread Brannon King
to configure or make for this to work right? I searched the newsgroup for this information, but was unsuccessful. Is there a link that explains it already available? Thanks for your time. __ Brannon King ¯¯

[sqlite] sqlite3_sleep

2006-05-17 Thread Brannon King
ers%40sqlite.org/msg14954.html ) ? Thanks for your time. ______ Brannon King ¯¯