[sqlite] Any way to disable transactional behavior?
I have a program that writes hundreds of thousands of statements that are logically unrelated, i.e. each is "transactional". The problem is if I don't do a BEGIN TRANSACTION and do my inserts, it takes absolutely forever for my program to finish (we're talking hours). If instead I do it in a single transaction (i.e. BEGIN TRANSACTION, 1 million inserts, END) it dramatically improves the time. Is there a way to get this behavior without transactions? The reason is most of the times the program is terminated by a user action and I don't get a chance to END the transaction. I'm considering doing "periodic transactions", i.e. buffer in my application X statements and club them together. Is there a better more SQLite idiomatic solution? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Comparing same data in two tables in two separate databases
Hello, I would like to compare two tables (of the same name) in two separate database files. I could conceivably change the situation such that I have 2 tables within the same database but different names. My table definition is: CREATE TABLE (SchoolId INTEGER, SchoolName TEXT, EnrollmentCount INTEGER); I essentially want to generate a new database file that will output the difference in EnrollmentCount for each SchoolName. It is possible that some rows will be missing in either of the databases, and in that case I still want to print the row. I'm a bit new to SQLite so I don't know exactly how to syntactically compare two databases and of course, there is the SQL of the comparison (also which I'm new to) but I think I'll be able to figure that part out. I hope I've explained my question is detail. Would love your help and suggestions.
[sqlite] Adding PRAGMA=UTF-8 makes INSERT slower?
Thanks R. Smith. I'm sparing the details primarily because my environment is unlike many others (I'm using C# to access SQLite). But, I will put more effort into my questions and I'll reply back with db and table definitions, along side a simple reproducible program. On Sat, Jul 18, 2015 at 7:33 AM, R.Smith wrote: > Hayden, I've been following your questions (to try and help) but I have to > comment - the questions are all exceedingly vague, it's really hard to help > with one hand tied behind our collective backs. > > This is not a complaint - I'm very sure you try to keep it short out of > kindness and consideration (not wanting to be a bother etc.), but you trim > the questions beyond comprehension. > > If the question regards performance, for instance, please post a schema or > some SQL that shows the problem. I know in your mind it "makes sense" when > you describe the situation, but it is extremely hard for us (who have no > insight into the dev environment) to follow exactly what is meant, plus the > fact that what you imagine might cause the problem may or may not be the > actual thing, so if not mentioning the other things that might be involved, > we have to guess, and as you have (no doubt) noticed from many of the > replies - we often guess very wrong. > > > > On 2015-07-18 07:37 AM, Hayden Livingston wrote: >> >> I was getting garble in my SQLite database, so I switched >> PRAGMA=UTF-16 on for my INSERT statements. These are getting prepared. >> >> I noticed my total time dramatically increased. >> >> I then switched to UTF-8 thinking it's the increased writes causing >> it, no noticeable difference, i.e. it's just as slow as PRAGMA=UTF-16. >> >> Removing PRAGMA from my INSERT statements brings back the performance. >> >> Any ideas? > > > A case in point, this latest question - there are so many things that > influence speed to do with encodings, such as custom collations, is this > table FTS or not?, What garble did you get?, Encoding cannot ever be changed > for any SQLite DB, so when you say you "switch" it, what do you mean? The > DB only ever stores bytes, so any retardation is likely during conversion. A > simple bit of SQL script / schemata will answer all these and remove any > guesswork. > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Adding PRAGMA=UTF-8 makes INSERT slower?
I was getting garble in my SQLite database, so I switched PRAGMA=UTF-16 on for my INSERT statements. These are getting prepared. I noticed my total time dramatically increased. I then switched to UTF-8 thinking it's the increased writes causing it, no noticeable difference, i.e. it's just as slow as PRAGMA=UTF-16. Removing PRAGMA from my INSERT statements brings back the performance. Any ideas?
[sqlite] storing data across multiple tables
Heh, each source defines their own table definition, and they do match sometimes, but sometimes they don't. People don't always query their columns, they usually just go find string in column, so I see your point, believe me. The programmer in me says what if they need to query > X ... then I'll be string parsing all the rows that their type belongs to. On Fri, Jul 17, 2015 at 9:46 AM, Simon Slavin wrote: > > On 17 Jul 2015, at 5:27pm, Hayden Livingston > wrote: > >> Sorry, they do not have the same definition. They are different >> definitions. I just want to view the data in a view such that all rows >> from all tables can be seen sorted by time. > > You have designed 600 to 800 different table definitions for one program ? > Really ? How big is your breain to be able to understand them all at the > same time ? > > It is far simpler to figure out how to put your data into one table than it > is to keep merging your tables every time you do a SELECT. > > Could you use an affinity of NONE in some columns and put whatever data you > like in them ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] storing data across multiple tables
Sorry, they do not have the same definition. They are different definitions. I just want to view the data in a view such that all rows from all tables can be seen sorted by time. Obviously SQLite cannot do this since the table definitions are different, and I'd have to write some application code. I'm trying to understand what's the best way to use SQLite for this purpose. It's almost as if I need a table that has "pointers" to rows of other tables. I'm quite astonished by the flexibility of SQLite, so maybe it is possible to shoe-horn such a feature? On Fri, Jul 17, 2015 at 3:48 AM, Simon Slavin wrote: > > On 17 Jul 2015, at 3:22am, Hayden Livingston > wrote: > >> So, In my application code I'm going to through loop through all the >> tables. The table count is usually high hundreds (~600-800) > > Why do you have so many tables which, if I understand your post right, have > the same column definitions ? You can't have thought up 800 different table > definitions for one program. Why isn't all your data in one big table using > an extra column to indicate its category ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Improving performance by removing MapParameter calls?
In my program I'm spending most of my time in this function: System.Data.SQLite!SQLiteStatement.MapParameter This function is doing string comparisons to map the correct parameter. I do put my SQL command text into a statement and prepare it. I was wondering if there is a way to do so for my parameters also. Obviously parameters vary, so it'd be nice to have a parameterized function? Or is that essentially what a stored procedure is?
[sqlite] storing data across multiple tables
Thanks, Keith for correcting my usage. I'm referring to tables when I mean database schema, so pardon my technical terminology. I have multiple tables of data already, say TableA, TableB. These tables have different representations, one may contain a TEXT column, the other may contain and INTEGER column, but all of them contain an INTEGER field called time, which is unique. I also want to be able to query the union of results TableA and TableB. This is more of an architecture question to get the best performance. Given the requirement to also show the data in a format that is less schematic and more free-form how do people achieve that. I thought of the case that I could double write my data into TableA and TableB, and then also "CommonTable", which is the union of all entries in TableA, and TableB. Of course since there column number and types may not match, I create a "representation" -- basically take all the columns of that entry and append them into a single string. so my CommonTable looks like: CREATE TABLE CommonTable (INTEGER TimeEntry, TEXT COMMONDATA) This seems wasteful in terms of space -- I was wondering if others had thoughts. On Thu, Jul 16, 2015 at 7:55 PM, Keith Medcalf wrote: >> I have different schema data that I'd like to store across tables, and >> it is sorted in chronological order. > > Please explain what you mean by "schema", and especially "schema data". In a > relational database there is only one schema per database. This single > schema describes the tables and indices, views, and triggers stored in that > particular database (and, for other database engines, there may be other > types of objects described in the schema). > > Some database engines provide "views" of the single schema and pretend that > these views comprise "multiple schemata", however, they are not. They are > simply a filtered view of the single schema for the database. With these > systems (such as Oracle, SQL Server, etc etc etc) the "schema" you choose > simply imposes a "filtered view" and "defaults" on certain attributes of the > real single schema. For example, there is an attribute (column) in the > schema called "schema name". > > When you choose a schema then all you get to see are the things where the > column [schema name] is equal to what you set, plus the dbo. When you create > a new object, the [schema name] is automagically set to be the name of the > schema you are choosing to use. The magic of the schema table queries make > it "appear" as if the items in your chosen [schema name] obscure the > remainder of the schema, but this is only an illusion created by the WHERE > clause. > >> But then I also want to be able to make queries across tables when I >> do a query that says after time X, I want it to show me all rows -- >> obviously different schema doesn't make this possible. >> >> So, In my application code I'm going to through loop through all the >> tables. The table count is usually high hundreds (~600-800) >> >> I'm wondering if other people do this kind of stuff. Or another option >> is to duplicate the data in some non schematized format (like a >> summary table of sorts that essentially duplicates every row in every >> table) > > I really don't understand what you are trying to accomplish. Are you truing > (for some unexplained reason) to "partition" data that otherwise would belong > in one table into a large number of tables for some reason -- for example, > instead of having a column [Colour] which describes the colour of the thing > the tuple is describing, instead have a bunch of tables called Red, Yellow, > Green, Blue (etc), so that you have to search all of them instead of just one? > > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] storing data across multiple tables
I have different schema data that I'd like to store across tables, and it is sorted in chronological order. But then I also want to be able to make queries across tables when I do a query that says after time X, I want it to show me all rows -- obviously different schema doesn't make this possible. So, In my application code I'm going to through loop through all the tables. The table count is usually high hundreds (~600-800) I'm wondering if other people do this kind of stuff. Or another option is to duplicate the data in some non schematized format (like a summary table of sorts that essentially duplicates every row in every table) Ideas? Improvements?
[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?
Thanks! On Thu, Jul 16, 2015 at 2:50 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/16/2015 02:46 PM, Hayden Livingston wrote: >> It seems that >> >> CREATE TABLE A( something varchar(255) ) >> >> it just >> >> CREATE TABLE A( something text ) >> >> Why have it at all? > > Because other code and other humans look at the database too. > Consider it documentation of intent. If it has no value to you, then > leave out the type. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1 > > iEYEARECAAYFAlWoJyIACgkQmOOfHg372QTu+wCghdSY6eDO5zXezGV+/xRXHHDr > rWUAn1XuuG65S/48qjyfx9TP/lzXYS0g > =Fzkn > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?
It seems that CREATE TABLE A( something varchar(255) ) it just CREATE TABLE A( something text ) Why have it at all?
[sqlite] Schema-less JSON SQLite DB?
I'm beginning to understand some of the implementation challenges here.
Eric, it'd be great to get that open sourced. I can tell you at least
I will jump and prototype on it.
Sergej and Roger, I do like this potential idea as well. Of course,
now it's a custom sqlite, but at least the job gets done. Do you think
you'll want to do this anytime soon as an open source project?
On Wed, Jul 15, 2015 at 2:55 PM, J Decker wrote:
> On Wed, Jul 15, 2015 at 10:03 AM, Roger Binns
> wrote:
>
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 07/15/2015 08:22 AM, Sergej Jure?ko wrote:
>> > What do you guys think? Is it stupid, could it be improved?
>>
>> I recommend looking at Mongodb and Postgres first to see how they do
>> queries. It would be better to be compatible with them where
>> practical, rather than being gratuitously different.
>>
>>
> (mentions postresql methods and MongoDB)
> http://thebuild.com/presentations/pg-as-nosql-pgday-fosdem-2013.pdf
> http://www.postgresql.org/docs/9.1/static/hstore.html
>
> My question is, how do you have a intelligent (orderly, structured) query
> of unstructured data?
> I can imagine ways to store and retrieve such things, but how do you query
> for 'give me the count of what I don't know is available' ?
>
>
> http://docs.mongodb.org/manual/reference/sql-comparison/ - good comparison
> of sorts...
>
> but then when it gets to how it get implemented... they're relying on
> external library isntead of 'SQL' or really any sort of query language.
> (day1-2 and day 3-4 links...)
> https://www.mongodb.com/blog/post/mongodb-vs-sql-day-1-2?jmp=docs&_ga=1.94603548.1409473473.1436992997
> https://www.mongodb.com/blog/post/mongodb-vs-sql-day-3-5?jmp=docs&_ga=1.94603548.1409473473.1436992997
>
> From an application standpoint, this doesn't resemble my interface to using
> SQL and getting results back into code; Java looks horrible. But then
> again my library was built as an abstraction around ODBC and cleverly hides
> details so applications can actually just do work
> http://api.mongodb.org/c/current/executing-command.html (the C interface to
> mongodb to execute a command)
>
> At least the hstore is a extension to the query language, and not a library.
>
> ---
> I guess I've been looking at this strictly from a SQL standpoint, rather
> than using sqlite3_* interfaces to do the work... since My library just has
> 3 commands... GetSQLConnection, DoSQLCommand, (okay and a set...
> GetSQLRecord, GetSQLNext, EndSQL) then using these, I can defiantly see how
> MonogoDB can be built as a layer on top of that using a hash storage
> technique...
>
> table object( objid int, parent_objid int, type int, name_id int, value
> varchar )
> table object_names( name_id int, name varchar unique )
>
> if they're grouped into some document also can add table doc( docid, name,
> root_objid )
>
> where type is
> 0 - array, and all object with this objid as it's parent_id is in this
> array, (ignore this_object's value; query of sub-data will ignore name...
> well actually the name should be the index into the array so the order can
> be preserved)
> 1 - object, (NULL value)
> 2 - data (use value as int, float, bool or string)
>
> I would also build a name dictionary and store a name_id instead of the
> literal name since typically Int comparisons are faster... at least in a
> well(long) named schema it would save a few bytes overall
>
> In the day 1-2 MongoDB link he uses a 'user' master object with
> 'contact_number' detail object...
>
> select_contact()
> {
> select objid from object where name = 'user', and type = 1/*object*/
> and parent=0
> for each object
> select * from object where name='contact_number' and type =
> 1/*object*/ and parent=objid
> /* do something to store in variant result data type */
> }
> viola JSON query in SQL via a library; that's certainly what the internals
> of MongoDB result in
>
> and again maybe RECURSIVE and WITH operators in sqlite's SQL might be
> useful for simplifying the looping
>
> // join user.contact.numbers .. users that have the same numbers
> join_contacts() {
> select objid from object where name='user' and type=1 and parent=0
> for each objid
> select objid as cn,value from object where name='contact_number'
> and type=1 and parent=objid
> for each cn /* contact number objid */
> results = select_by_number( value ) /* routine which is a
> select user.contact_number=value */
> foreach result
> if( result.objid == objid ) continue /* skip this one */
> /* add to variant result */
> }
> some of the above could be selected with an inner join into one select, but
> for simplicity broke it into more atomic steps...
>
> /* och = object contact number, ou = object user */
>select ocn.value,ocn.objid as contact_objid from object as ou join
> object as ocn on ocn.parent_objid=ou.objid
[sqlite] Schema-less JSON SQLite DB?
that does indeed look interesting. Embedded DB is the only problem here. On Mon, Jul 13, 2015 at 11:01 PM, Darren Duncan wrote: > Have a look at PostgreSQL 9.4 as well and its new JSONB data type. Gives > you the goods of relational and hierarchical databases in one place, > including the querying and indexing. -- Darren Duncan > > > On 2015-07-13 5:43 PM, Hayden Livingston wrote: >> >> Is there a concept of a schema-less JSON SQLite DB? >> >> My reason is simple: versioning. We have lot of business metrics that >> get updated let's say once a month, and we need to be agile to get >> them. Right now, we just put the version in the SQLite file, and then >> make sure no queries cross the boundaries. >> >> Secondly, we have requirements for slightly hierarchal data, i.e. >> mostly row form, but then some guy wants to put an object. >> >> What's the SQLite community heading towards if at all? >> >> Do others have experiences and requirements similar to this? > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Schema-less JSON SQLite DB?
Wow, this is the killer feature no-one told me about. I can get rid of types? How does this work under-the-hood? SELECT * FROM TABLE WHERE FooColumn > 50 And I've stored "Something" in that column in some row. What's the behavior? I don't want to take up your time, so if there's a document I can read about it'd be great. On Mon, Jul 13, 2015 at 8:34 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/13/2015 08:00 PM, Hayden Livingston wrote: >> Does your code also map object hierarchies in json? > > Yes, but thankfully I don't have much of them. Essentially the top > level of the object has a unique id (SQLite allocated), and then other > tables are used to join zero or more child objects to the top level. > >> What general format do you use? > > Ultimately I use Python dictionaries which are supersets of JSON > objects. Some (ugly) code can convert both ways > >> Each object type gets stored in a separate table? > > In my case yes but that is because the underlying data represents > known entities and was actually originally in Postgres and then > exported to hideous inconsistent XML which I then convert/denormalise > back into JSON. > > Do remember that SQLite does not require you to specify a type for > each column, nor does it care about the values in a column being > different types between rows. That means I don't have to worry about > types, only the big picture top level of something being an object, a > list, or a scalar. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1 > > iEYEARECAAYFAlWkg0MACgkQmOOfHg372QRTbQCfRobbh4uU1Eqf0SD9LJxABFYj > iv8AoKUvXNQEgGVvmPiZ/tQgHyU7Q0yL > =S7AM > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Schema-less JSON SQLite DB?
We use SQLite as a library and we don't want to migrate to a server situation. So it seems using the month-to-month approach you outline is reasonable and not outrageous. Good to know. Thanks for the tip on schema modifications which get automatically upgraded on read. This is a great feature. I think I might try that out for my application. Does your code also map object hierarchies in json? What general format do you use? Each object type gets stored in a separate table? On Mon, Jul 13, 2015 at 7:03 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/13/2015 05:43 PM, Hayden Livingston wrote: >> Is there a concept of a schema-less JSON SQLite DB? > > What exactly is it you want? A schema-less database? A JSON > database? Using SQLite to store JSON? (It is unclear if you mean > something like SQLite, or SQLite itself). Something small and light? > A library not a server? > > Be aware that you can dynamically update the SQLite schema, and that > it takes no time to do so. Another database I won't name locks the > database and rewrites each record. This can take hours or even days. > SQLite just updates the schema table, and fills in defaults for > records written before the schema change, on reads. I wrote some code > the other day that takes JSON and does the schema manipulation, > although it is annoying. (It would be really great if SQLite > supported arrays like Postgres does.) > > You can also store arbitrary JSON in a column. You won't be able to > query it effectively, but you can duplicate values into columns you > can do queries and indexes on. > >> My reason is simple: versioning. We have lot of business metrics >> that get updated let's say once a month, and we need to be agile to >> get them. Right now, we just put the version in the SQLite file, >> and then make sure no queries cross the boundaries. > > You can also use multiple databases, and attach them. For example if > you operate on a month by month basis, then you can put each month's > data in a separate SQLite file, then attach last month's as > 'lastmonth', and use in queries (eg SELECT blah blah WHERE bar.foo > > lastmonth.bar.foo) > >> Do others have experiences and requirements similar to this? > > All the time. I use SQLite when I don't need networked access, a > running database server, and need less things that can go wrong. > Mapping JSON into this is painful but possible. > > When I can use a database server, I prefer Mongodb as it is very good > at arbitrary JSON in, the same arbitrary JSON back out. It is > especially pleasant that the query syntax has the same shape as the > underlying JSON data. Also JSON maps trivially to Python which I use > the most. (Note however that Mongodb does have some issues, but so > does much other software out there. Production use does require > effort & planning as does other software.) > > In the longer term Postgres is getting increasingly better JSON > support. Hopefully it will give Mongodb a run for its money soon. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1 > > iEYEARECAAYFAlWkbfsACgkQmOOfHg372QTFiwCgzzf5fEzfJdWn84bk0e1fusGE > qeAAnAhR+sHb39Gsha2Owq3SXsdZVRex > =9jcT > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Schema-less JSON SQLite DB?
Is there a concept of a schema-less JSON SQLite DB? My reason is simple: versioning. We have lot of business metrics that get updated let's say once a month, and we need to be agile to get them. Right now, we just put the version in the SQLite file, and then make sure no queries cross the boundaries. Secondly, we have requirements for slightly hierarchal data, i.e. mostly row form, but then some guy wants to put an object. What's the SQLite community heading towards if at all? Do others have experiences and requirements similar to this?
[sqlite] Storing opaque data, but would like sorted indexes on some fields
I have a binary format that is effectively structured data. I currently have multiple indexes but none of them are sorted, because in my toy system ORDER BY's are not supported, an implicit ORDER BY time of record inserted exists because it is a single threaded application. My indexing story is that I know a priori what my index will be, and I just double write them. I want to support ORDER BY on basically one additional field, and I'm having a hard time coming up with a solution that doesn't involved effectively writing on my B+Tree which has knowledge of my serialization binary format. I've thought to myself, maybe I could just store a duplicate copy of the data sorted by this field into SQLite. The data footprint is not small, but I'm willing to pay that disk cost if that's the only option. My only concern with moving to SQLite is performance design decisions that are built into a database. My system is basically only doing the work needed to iterate over my data. No concurrency, sequential reads. When/If I move to SQLite, what kind of things will I start to pay for? Concurrency? Other fluff data structure needs for SQLite? Obviously I imagine that whatever field I choose to get a sorted index on, I expose as it as a field. Then the non SQLite question: how much work would it be to duplicate a B-tree like data structure for my binary data? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite for write only workload, splitting databases into smaller files, but need to query it as a whole
Ok. But I'm wondering how GROUPBY and stuff will work? On Sat, May 3, 2014 at 7:13 AM, Petite Abeille <petite.abei...@gmail.com>wrote: > > On May 3, 2014, at 3:40 PM, Hayden Livingston <halivings...@gmail.com> > wrote: > > > This looks promising. I sooo wish it didn't have a limit to number of > > databases. > > 10 by default if I recall properly. > > Can be perhaps be increased to 62 at most: > > http://www.sqlite.org/limits.html > > > But I think I could reasonably do something like coalesce the > > databases into a new database once every 2 hours. I also need to generate > > some code to figure out how to address the tables which I guess means > I'll > > have to do an N way JOIN? > > The table names stay the same. Each attached database can have a unique > name. > > So, for example: > > attach … as attached01; > attach … as attached02; > ... > > with > DataSet > as > ( > select count( * ) as count > from attached01.table > > union all > select count( * ) as count > from attached01.table > > union all > ... > ) > select sum( count ) as sum > from DataSet; > > Also: > > http://www.sqlite.org/pragma.html#pragma_database_list > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite for write only workload, splitting databases into smaller files, but need to query it as a whole
This looks promising. I sooo wish it didn't have a limit to number of databases. But I think I could reasonably do something like coalesce the databases into a new database once every 2 hours. I also need to generate some code to figure out how to address the tables which I guess means I'll have to do an N way JOIN? On Sat, May 3, 2014 at 6:16 AM, Petite Abeille <petite.abei...@gmail.com>wrote: > > On May 3, 2014, at 2:59 PM, Hayden Livingston <halivings...@gmail.com> > wrote: > > > Thoughts? > > Take a look at ‘ATTACH’, it might help: > > http://www.sqlite.org/lang_attach.html > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite for write only workload, splitting databases into smaller files, but need to query it as a whole
That's clever. I'm thinking how I'll go about this .. essentially the filename is devised by time splits. I could do what you're saying but then I can't move the file. Basically, the good part is that our warehousing department (which is what this is used for) can look at order inventories every 5 minutes (that's the current split). This way I'll have to somehow stop the writes for a few seconds, copy the file into a new file and then start writing again. Not sure if this will work out, but I'm going to keep in my head. On Sat, May 3, 2014 at 6:06 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 3 May 2014, at 1:59pm, Hayden Livingston <halivings...@gmail.com> > wrote: > > > My workload is quite straightforward, I have a multi-threaded application > > that logs to this file but from within any single thread at any given > time. > > So from SQLite's perspective only one person will be writing to this > > database and nobody will ever read from it. > > > > The reading happens after the entire database is written to. > > > > Is this a use case for SQLite? It seems like the benefit I get from > SQLite > > is that I can do SQL on my previously textual data and optionally add > > indexes. > > The benefit you'd get from SQL is the ability to look up a specific entry > easily rather than having to look through an entire file to find the entry > you want. > > > HOWEVER, this is my catch -- I sometimes have the need to query two > > databases at the same time. In fact, sometimes I might need to query > 100's > > of these small databases. Would you just recommend querying the 100's in > > parallel and then joining them in memory or something? > > Had you considered merging them all into one big database ? You currently > have some sort of variable that you use to devise a filename. Turn that > into a column name which is part of a UNIQUE key. Then, when you want to > run a query all your data is in one big database and you can query every > 'database' at the same time and return the answers in a single list. > > You should be able to get enough data together to test relatively easily. > Write an importer that takes your existing text files and adds their data > into one big SQLite database. Then you can try numerous SELECT queries > using the SQLite shell tool (without having to write different software > each time) and see what indexes would be useful and how fast they'd give > you results. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using SQLite for write only workload, splitting databases into smaller files, but need to query it as a whole
I have a situation where I store a data in a text file with tabs, and it's becoming really quite unwieldy to use this. My workload is quite straightforward, I have a multi-threaded application that logs to this file but from within any single thread at any given time. So from SQLite's perspective only one person will be writing to this database and nobody will ever read from it. The reading happens after the entire database is written to. Is this a use case for SQLite? It seems like the benefit I get from SQLite is that I can do SQL on my previously textual data and optionally add indexes. HOWEVER, this is my catch -- I sometimes have the need to query two databases at the same time. In fact, sometimes I might need to query 100's of these small databases. Would you just recommend querying the 100's in parallel and then joining them in memory or something? Has anybody ever needed to do this? I can only think of querying the database, storing it in memory and then doing things to it. But then I lose the flexibility of SQL. Thoughts? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting rows as "groups" -- "pivots"?
If I have my data structured like Col1 "FooId", "Value1", "Value2", "Value3" I can do group by (Col1) and show this data on a graph. Imagine Col1 is the timestamp field, I could split it into hour groups and show rates of somethings. Like orders per hour rate. On Thu, Dec 5, 2013 at 4:29 PM, Igor Tandetnik <i...@tandetnik.org> wrote: > On 12/5/2013 7:16 PM, Hayden Livingston wrote: > >> I suppose I should have stated my goal even further. I'm putting them in >> sql so that I can query it row by row (i.e. operationId by operationId), >> as >> opposed to SELECT all operationIds, then foreach operation id select all >> rows where operationid = ... >> > > Query for what, exactly? Show an example of the kind of data you want to > get out of that database. > > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting rows as "groups" -- "pivots"?
I suppose I should have stated my goal even further. I'm putting them in sql so that I can query it row by row (i.e. operationId by operationId), as opposed to SELECT all operationIds, then foreach operation id select all rows where operationid = ... On Thu, Dec 5, 2013 at 1:44 PM, Igor Tandetnik <i...@tandetnik.org> wrote: > On 12/5/2013 4:31 PM, Hayden Livingston wrote: > >> --> End Goal: >> >> A SQL Table: >> >> FooId,SomeStringValue,SomeIntValue,SomeOtherColumn, >> WhateverColumnNameItNeedsTo, >> > > You do not want a table with an open-ended set of columns - SQL doesn't > work that way. You want a fixed schema; let the number of rows be > open-ended. Something like this: > > ComponentId, OperationId, ColumnName, Value > > See also http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2% > 80%93value_model > -- > Igor Tandetnik > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting rows as "groups" -- "pivots"?
Ok. We have a bunch of different components in our system that write about a particular investment. These components know nothing about each other, except the item they are operating on. Let's take 3 components: ComponentA, ComponentB, ComponentC -- all of these write a "FLAT FILE"! We then parse each file in parallel line-by-line. Each component can choose to write whatever data it wants to (in fact they do, each component is a different team in my organization). But we've all agreed on an "OperationID", which happens to be a 128-bit unique value. Each component can write "MULTIPLE" rows of information and I don't know how many rows it can write, but they will all be of the same schema. ComponentA can choose to write: FooId, SomeStringValue, SomeIntValue (and it can write many rows) ComponentB can choose to write: FooId, SomeOtherColumn (and it can write many rows) ComponentC can choose to write: FooId, WhateverColumnNameItNeedsTo (and it can write many rows) --> End Goal: A SQL Table: FooId,SomeStringValue,SomeIntValue,SomeOtherColumn,WhateverColumnNameItNeedsTo, FooId2,. So is what RSmith is suggesting what I really want? I've spent so many hours on this.I'm actually clueless now as well. On Thu, Dec 5, 2013 at 11:45 AM, RSmith <rsm...@rsweb.co.za> wrote: > > Apologies, my mail is slow today, did not notice this thread had > progressed significantly before I posted - please ignore previous. > > I'm with Igor though, the multi-table layout you now have is even less > convenient than the matrix - It's equally dispersed data only now you have > to join 3 tables for the result. Any chance you could share with us the > exact thing you are trying to store and the exact resulting knowledge you > wish to deduce from the stored data? Maybe we can come up with more > helpful suggestions (as opposed to just looking puzzled!). > > Cheers, > Ryan > > > On 2013/12/05 20:54, Igor Tandetnik wrote: > >> On 12/5/2013 1:43 PM, Hayden Livingston wrote: >> >>> Yes, are moving our sparse matrix to different tables: >>> >>> Id | RelationalIdentifier | ColA >>> 1aX >>> 2bA >>> >>> Id | RelationalIdentifier | ColB >>> 1aY >>> 2bB >>> >>> Id | RelationalIdentifier | ColC >>> 1aZ >>> 2bC >>> >> >> Why? Personally, I'd have one table: either >> >> Id, RelationalIdentifier, ColA, ColB, ColC >> 1, a, X, Y, Z >> 2, b, A, B, C >> >> or >> >> Id, RelationalIdentifier, Col, Value >> 1, a, ColA, X >> 1, a, ColB, Y >> 1, a, ColC, Z >> 2, b, ColA, A >> 2, b, ColB, B >> 2, b, ColC, C >> >> depending on how open-ended the list of columns is. It's also not clear >> what the purpose of Id and RelationalIdentifier is; they appear duplicative >> of each other. >> >> How can I adapt your query to this new table schema? >>> >> >> select RelationalIdentifier, ColA, ColB, ColC >> from TableA join TableB using (RelationalIdentifier) join TableC using >> (RelationalIdentifier); >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting rows as "groups" -- "pivots"?
Thanks, Igor. Yes, are moving our sparse matrix to different tables: Id | RelationalIdentifier | ColA 1aX 2bA Id | RelationalIdentifier | ColB 1aY 2bB Id | RelationalIdentifier | ColC 1aZ 2bC How can I adapt your query to this new table schema? On Thu, Dec 5, 2013 at 10:33 AM, Igor Tandetnik <i...@tandetnik.org> wrote: > On 12/5/2013 1:15 PM, Hayden Livingston wrote: > >> I have a table schema such like >> >> ID | Col1 | Col2 | Col3 | Value >> 1 anull nullX >> 2 null a nullY >> 3 null null a Z >> 4 b nullnull A >> 5 null b nullB >> 6 null null b C >> >> Right now these are in the same table (they may be different tables in the >> near future, but I don't think that impacts this discussion) >> >> I want to "PIVOT" this data such that: >> >> A | Value | Value | Value >> a X Y Z >> b A B C >> > > select coalesce(Col1, Col2, Col3) as A, > max(case when Col1 is null then null else Value end) as Value1, > max(case when Col2 is null then null else Value end) as Value2, > max(case when Col3 is null then null else Value end) as Value3 > from MyTable group by A; > > I must say you chose a very inconvenient table schema. What's with the > sparse matrix? What kind of queries is this good for? > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Selecting rows as "groups" -- "pivots"?
I have a table schema such like ID | Col1 | Col2 | Col3 | Value 1 anull nullX 2 null a nullY 3 null null a Z 4 b nullnull A 5 null b nullB 6 null null b C Right now these are in the same table (they may be different tables in the near future, but I don't think that impacts this discussion) I want to "PIVOT" this data such that: A | Value | Value | Value a X Y Z b A B C Searching on the internet says this can be done in SQLite using CASE statements? But what happens I don't know the row id values (i.e. a, b, etc)? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Percentile function in SQLite 3.8.0 (for the .NET version)
Yeah they were going to the spam folder! Thanks.
I've got a few suggestions to work through. I'm going to wait a bit for the
PERCENTILE function already present in future SQLite releases for .NET
If it takes a while to get released, I might venture into this!
On Mon, Dec 2, 2013 at 9:35 AM, RSmith <rsm...@rsweb.co.za> wrote:
> There have been a few responses to your question, I count at least 3 from
> different people all with working suggestions.
>
> Are you sure you are getting the list emails? Maybe check spam folders etc.
>
> Or are you unsatisfied with the responses?
>
>
>
> On 2013/12/02 19:30, Hayden Livingston wrote:
>
>> Is there any documentation or use case for percentile queries? I've only
>> seen the source code checked in by "drh".
>>
>> Is it like the EXCEL 2013 function?
>>
>> I have a table {operationId, latency) with a set of rows that have
>> floating
>> values for latencies of a given operation.
>>
>> I want to find the 25th %ile latency for this rowset.
>>
>> (A) How do I create a Percentile Query?
>> (B) How do I make it work for the SQLite System.Data.Sqlite adapter in
>> .NET?
>>
>> Many thanks!
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Percentile function in SQLite 3.8.0 (for the .NET version)
Ok great. Do you know if the 1.0.90.0 will be out anytime soon? That is, this month or so ...? On Mon, Dec 2, 2013 at 11:54 AM, Joe Mistachkin <sql...@mistachkin.com>wrote: > > Hayden Livingston wrote: > > > > (A) How do I create a Percentile Query? > > > > First, the percentile extension function needs to be loaded into SQLite, > either statically or dynamically, as described here: > > https://www.sqlite.org/loadext.html > > The necessary source code can be found in the repository, here: > > https://www.sqlite.org/src/artifact?ci=trunk=ext/misc/percentile.c > > After compiling and loading the extension, the function can be used in > queries, e.g.: > > CREATE TABLE t1(x); > INSERT INTO t1 VALUES(1),(4),(6),(7),(8),(9),(11),(11),(11); > SELECT percentile(x,25) FROM t1; > > > > > (B) How do I make it work for the SQLite System.Data.Sqlite adapter in > .NET? > > > > This involves modifying the SQLite.Interop project to include the > percentile > extension. The next release (1.0.90.0) will now include this extension by > default. > > -- > Joe Mistachkin > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Percentile function in SQLite 3.8.0 (for the .NET version)
Is there any documentation or use case for percentile queries? I've only
seen the source code checked in by "drh".
Is it like the EXCEL 2013 function?
I have a table {operationId, latency) with a set of rows that have floating
values for latencies of a given operation.
I want to find the 25th %ile latency for this rowset.
(A) How do I create a Percentile Query?
(B) How do I make it work for the SQLite System.Data.Sqlite adapter in .NET?
Many thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Percentile function in SQLite 3.8.0 (for the .NET version)
Is there any documentation or use case for percentile queries? I've only
seen the source code checked in by "drh".
Is it like the EXCEL 2013 function?
I have a table {operationId, latency) with a set of rows that have floating
values for latencies of a given operation.
I want to find the 25th %ile latency for this rowset.
(A) How do I create a Percentile Query?
(B) How do I make it work for the SQLite System.Data.Sqlite adapter in .NET?
Many thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
