Re: [sqlite] Split Function for SQLite?

On Tue, Aug 23, 2011 at 7:10 AM, Gregory Moore wrote: > I need to split up a list of items in a single row so they each have > their own row. > You can read about my trick query solving partly this task with a trick

Re: [sqlite] Split Function for SQLite?

If your programming language is C or C++, it's trivial to register a function with SQLite at runtime that can then be used in your queries. This does not require hacking SQLite itself. As noted, you can't return multiple rows per database row via a function. That's not how SQL works; it is "set"

Re: [sqlite] saving pragma states in database

Thanks for the reply, > You could always create a table that stores the pragma values in which > you're interested, then have code that checks on start up to set those > pragmas to those values. I was thinking the same thing, but only as a last resort. It seems like the database should be able

Re: [sqlite] Split Function for SQLite?

> Did you see the code in the link I provided?  It's a function that can be > added to SQL. Note, it's not SQL. SQL doesn't support "adding functions". SQL is all about SELECT/INSERT/UPDATE/DELETE + DDL commands (like CREATE TABLE). That's it. So the link you provided is a function that can be

Re: [sqlite] Split Function for SQLite?

I don't know know whether it's a SQLite "issue" or not. Did you see the code in the link I provided? It's a function that can be added to SQL. I just need to find out whether it can be added to SQLite and if so, then how to add it. I'd like to know before I try it whether I would run the

Re: [sqlite] Split Function for SQLite?

On 8/24/2011 9:05 PM, Gregory Moore wrote: > I'm thinking it's parameters would be the name of a column and a character to > designate where the string wold be separated. It would return multiple rows > and each row would contain a piece of the string. > > In other word, take a table like this:

Re: [sqlite] Split Function for SQLite?

I'm thinking it's parameters would be the name of a column and a character to designate where the string wold be separated. It would return multiple rows and each row would contain a piece of the string. In other word, take a table like this: c1 c2 --- 1 a; b; c run a statement like

Re: [sqlite] Split Function for SQLite?

On 8/24/2011 9:36 PM, Gregory Moore wrote: > Thanks for answering! Can this not be added as a function? What parameters would such a function take, and more interestingly, what would its return value be? -- Igor Tandetnik ___ sqlite-users mailing

Re: [sqlite] Split Function for SQLite?

Thanks for answering! Can this not be added as a function? On Aug 22, 2011, at 10:43 PM, Igor Tandetnik wrote: > Gregory Moore wrote: >> I need to split up a list of items in a single row so they each have >> their own row. >> >> Basically I need to take this: >> >>

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

On 24 Aug 2011, at 11:18pm, GB wrote: > Well, that is exactly what the sqlite_stat2 table is meant for. It's > information is supposed to make the query planner able to decide upon > the usefulness of an index. Unfortunately, histogram information is not > collected for the implicit rowid

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

Simon Slavin schrieb am 24.08.2011 23:33: > On 24 Aug 2011, at 9:59pm, GB wrote: > >> Simon Slavin schrieb am 24.08.2011 22:38: >> >>> SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND >>> createdAt>= '2011-08-01' createdAt<= '2011-08-02' >> Thank you for your thoughts but I

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

On 24 Aug 2011, at 9:59pm, GB wrote: > Simon Slavin schrieb am 24.08.2011 22:38: > >> SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND >> createdAt>= '2011-08-01' createdAt<= '2011-08-02' > > Thank you for your thoughts but I already tried this with no different > results

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

Simon Slavin schrieb am 24.08.2011 22:38: > On 24 Aug 2011, at 6:59pm, GB wrote: > >> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND >> createdAt BETWEEN '2011-08-01' AND '2011-08-02' > Just out of curiosity, try changing both the BETWEEN formulations so it says > > SELECT

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

On 24 Aug 2011, at 6:59pm, GB wrote: > SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND > createdAt BETWEEN '2011-08-01' AND '2011-08-02' Just out of curiosity, try changing both the BETWEEN formulations so it says SELECT itemID FROM t WHERE itemID >= 100 AND itemID <=

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

No, SQLite (as well as most other database systems) does a more elaborate evaluation. I breaks the statement apart into subterms and tries to determine which one makes the most beneficial use of an index so the order of the statement does not matter. See http://www.sqlite.org/optoverview.html

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

Igor Tandetnik schrieb am 24.08.2011 20:20: > On 8/24/2011 1:59 PM, GB wrote: >> When perfoming a Statement like this: >> >> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND >> createdAt BETWEEN '2011-08-01' AND '2011-08-02' >> >> the analyzer always chooses the rowid index which

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

Don't know how SQLite should behave in this case, but seems logical to me that A and B would force that A is always evaluated, and B is evaluated only if A is true. I would change the order of the two betweens: SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND '2011-08-02' AND

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

On 8/24/2011 1:59 PM, GB wrote: > When perfoming a Statement like this: > > SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND > createdAt BETWEEN '2011-08-01' AND '2011-08-02' > > the analyzer always chooses the rowid index which results in a scan over > one million rows. It would

[sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

Hi all, I have a table like this: CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME); CREATE INDEX createIdx on t(createdAt); SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with current content. When perfoming a Statement like this: SELECT itemID FROM t WHERE

Re: [sqlite] Shell tool locks database ? AFP access a problem ?

On 24 Aug 2011, at 3:07pm, Richard Hipp wrote: > You've run up against limitations of AFP. Or, more precisely, you've run up > against the performance/concurrency tradeoffs that are inherent in any > network filesystem. > > Apple has contributed working (though wildly complex) code that allows

Re: [sqlite] Shell tool locks database ? AFP access a problem ?

On Wed, Aug 24, 2011 at 6:14 PM, Pavel Ivanov wrote: > This is completely unrelated because Oracle works differently. > Regardless of the reason for the specific behaviours, the symptoms are similar, and they are both locking-related problems. Maybe off-topic, admittedly,

Re: [sqlite] Shell tool locks database ? AFP access a problem ?

> i saw the same behaviour with Oracle's CLI client > ("sqlplus") - as long as i had sqlplus opened and connected, my PHP pages > couldn't insert any data. > > i.e. this type of problem isn't limited to sqlite3. This is completely unrelated because Oracle works differently. And if you saw this

Re: [sqlite] Shell tool locks database ? AFP access a problem ?

On Wed, Aug 24, 2011 at 3:51 PM, Simon Slavin wrote: > SQLite3::query(): Unable to execute statement: database is locked in > [...]readrec.php > For what it's worth: while porting a customer's PHP app from MySQL to Oracle early this year, i saw the same behaviour with

Re: [sqlite] Shell tool locks database ? AFP access a problem ?

On Wed, Aug 24, 2011 at 9:51 AM, Simon Slavin wrote: > On a Mac running OS X 10.6.8 Server. Being accessed by another Mac running > 10.6.8 Client. > > The shell tool I'm using is the one found on a standard installation of OS > X 10.6.8. It accesses the database by

[sqlite] Shell tool locks database ? AFP access a problem ?

On a Mac running OS X 10.6.8 Server. Being accessed by another Mac running 10.6.8 Client. The shell tool I'm using is the one found on a standard installation of OS X 10.6.8. It accesses the database by opening the file across an AFP connection to the server. The shell tool can read and

Re: [sqlite] saving pragma states in database

You could always create a table that stores the pragma values in which you're interested, then have code that checks on start up to set those pragmas to those values. On Tue, Aug 23, 2011 at 1:24 PM, Erik Lechak wrote: > Hello all, > > Is there a way to save pragma states to

Re: [sqlite] Re indexing (if such a thing exist) performance

On 24 Aug 2011, at 1:49pm, LiranR wrote: > I meant that the two PK columns are actually primary key of (id, > timestamp).. Okay. That's probably not what you want to do. If you think about your data you probably want just one of them as the primary key. The only way you would need both of

Re: [sqlite] Autoincrement failure

On 24 Aug 2011, at 1:13pm, a.azzol...@custom.it wrote: > On my laptop integrity_check fail > >> PRAGMA integrity_check >> returns >> >> *** in database main *** >> rowid 0 missing from index JournalDateIndex >> rowid 0 missing from index sqlite_autoindex_Journal_1 >> wrong # of entries in

Re: [sqlite] Re indexing (if such a thing exist) performance

Thanks... I meant that the two PK columns are actually primary key of (id, timestamp).. What i don't understand, is why do i need primary keys as timeStamp if not to make an index tree of my table rows ? Does the PK doesn't build an index tree of the rows so i can get a row faster? Simon

Re: [sqlite] Re indexing (if such a thing exist) performance

On 24 Aug 2011, at 12:39pm, LiranR wrote: > ID (PK) | TimeStamp (PK) | data | data | and data ... > - >1 |101| float | float | float >2 |102| float | float |

Re: [sqlite] Autoincrement failure

On my laptop integrity_check fail > PRAGMA integrity_check > returns > > *** in database main *** > rowid 0 missing from index JournalDateIndex > rowid 0 missing from index sqlite_autoindex_Journal_1 > wrong # of entries in index JournalDateIndex > wrong # of entries in index

Re: [sqlite] Re indexing (if such a thing exist) performance

You won't know until you test it. It varies for all situations. You don't know if you have a problem until you test it. Is there something stopping you from testing? What you may not understand that it's not "redindexing" -- it's deleting one node from the index and inserting another. So

Re: [sqlite] Re indexing (if such a thing exist) performance

mmm... I didnt state a problem, I stated a question: How much time will it take to reindex the new timestamp? As you can see, i do only update to the row. The things that changed are the data and the time stamp. Because the time stamp is PK i guess it will have to reindex the table, no? How bad

Re: [sqlite] Re indexing (if such a thing exist) performance

And...if you don't use ID at all you can get rid of it. CREATE TABLE mystuff (TimeStamp INTEGER PRIMARY KEY,Data1 FLOAT, ...) TimeStamp will be an alias for rowid. http://www.sqlite.org/autoinc.html That will get rid of one index and one field which will save you at least 16MB of

Re: [sqlite] Re indexing (if such a thing exist) performance

What you're saying makes sense. But you haven't stated a problem... Are you far enough along you can show timing beween 1st and 2nd queue fills? How long does it take you to insert your first million? How long does it take you to insert your second million? Michael D. Black Senior

Re: [sqlite] Re indexing (if such a thing exist) performance

I'd like to hear what you're doing and why you think SQLite is the way to do it. And what are your speed requirements? You are apparently worried about speed but never tested to see if SQLite was fast enough. You could've tested it yourself in a matter of an hour or so. I imagine an update

Re: [sqlite] Re indexing (if such a thing exist) performance

I use: ID (PK) | TimeStamp (PK) | data | data | and data ... - 1 |101 | float | float | float 2 |102 | float | float | float 3 | 3

Re: [sqlite] Re indexing (if such a thing exist) performance

On Wed, 24 Aug 2011 02:58:58 -0700 (PDT), LiranR wrote: > > Hi, Thanks for the answer, but i think you didn't understand what i asked. > > I use primary key index in my table. > When i finish to fill the table, row after row, i want to start all over > again and update

Re: [sqlite] Re indexing (if such a thing exist) performance

Hello, Do you truncate the table before iterating again ? I don't think it really effect performance, if you start from 1 or 1001. They are just number. You don't need to re-index your table. Indexes are created automatically at time of insertion/update/delete. So even if you delete old

Re: [sqlite] Re indexing (if such a thing exist) performance

Hi, Thanks for the answer, but i think you didn't understand what i asked. I use primary key index in my table. When i finish to fill the table, row after row, i want to start all over again and update the table from row 1 to row 100. The difference is that in the second time, the index of

Re: [sqlite] Re indexing (if such a thing exist) performance

Hello, IF you create index Primary key it automatically index the Information, but if not you can always create a separate index for your field(s). When Index are present at time of insertion or update index will also get update to include information. That do not REindex complete table. So

[sqlite] Re indexing (if such a thing exist) performance

Hi all!! In my project, i insert rows, one by one, to a fixed size table (100 rows for example). When i reach the end of the table, i enter data to the first row and then the seocond and so on. If i have indexes (primary keys), lets say from 1 to 100, and now i reached the end, and update