Re: [sqlite] [sqlite-dev] Implementing per-value custom types
That's not true. A 64 bit floating point number and an 64 bit integer can
be represented by the exact same 64 bit pattern, and no-one would suggest
they're the same value. You can have those two differently typed although
identical bit values in the same SQLite column. The data identifying the
representation of those datums is integral to that value and doesn't belong
in a different column as you say. Other SQL based systems also allow
differently represented (typed) values to appear in the same column.
The domain of a column can logically incorporate these different kinds of
values by introducing the concept of subtype. For instance in SQLite there
are something like 6 different representations of integer of between 1 and
64 bits. Each one of those is a different type in the sense of having a
different representation due to the number of bits they use and being
limited to a different set of numbers. A 1 bit integer is a subtype of a 64
bit integer since the set of numbers allowed by the 1 bit integer is {0, 1}
are contained with the set of numbers allowed by 64 bit integers, ie
{0..2^64-1}. If the column has a domain of integer then all these values
are valid since they're all integers. There is no logical or formal reason
why this can't be extended further to allow arbitrary subtypes according to
the user's wishes.
You can have the same 64 bits of data represent 4 different values in
SQLite: integer, double, string and blob (I'm assuming SQLite can store a
blob in 8 bytes). They are not treated as equal by SQLite because they have
different types. There is no reason why we should be limited to those 4
types in SQLite. Many SQL based system allow users to define their own
types. What I'm proposing is just implementing the same thing in SQLite.
There is nothing in the relational model that disallows this. You're
assuming that because columns have a domain or type, then that domain must
have a fixed representation in the database implementation. The relational
model says nothing about how the data is represented or what kind of data
can be stored.
On Tue, Nov 25, 2014 at 5:20 PM, James K. Lowden jklow...@schemamania.org
wrote:
On Tue, 25 Nov 2014 04:41:51 -0800
Darko Volaric li...@darko.org wrote:
I have a need to implement per-value custom typing in SQLite. In my
case I may have the integer value 1234 appear in two rows in the same
column, but they are different in that they have completely different
meanings because they have different types although they have the
same representation in the database.
A column is a type. Two values of the same type *must* represent the
same thing. That's how SQL works.
When you say 1234 may represent two different things in the same
column, what you really mean is that 1234 doesn't stand for the whole
thing, that the column isn't the whole type (as you conceive it).
That's fine; you need another column to discriminate between them, to
capture that whole type. Each column-component of that type is itself
a type, just as a street name is part of a postal address.
As a practical example of what that's true, consider this list:
anything
cat
green
jogging
We can sort that anything column as *strings*, but what if each one is
1234 in the database? Even if they are different values, how do you
compare green to jogging? Which one comes first? How should a join
work?
If that doesn't convince you, please understand I'm not expressing an
opinion. I'm pointing out a basic tenet of the relational model. I
can recommend good references on the subject.
If you represent your things, whatever they are, in the model according
to its rules, you will find you don't need to extend the type system.
Nothing good awaits you if you attempt to extend it without first
understanding it.
HTH.
--jkl
___
sqlite-dev mailing list
sqlite-...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Implementing per-value custom types
Hi Darko,
Firstly, kindly keep this to the sqlite-users forum and not on the dev forum (the devs read this too, the difference being simply
that this one exists to help you, the other one is to discuss development stuff, not to help anyone).
Secondly, you are confusing two things. You are arguing about what /CAN/ be done while James tried to explain what /Should/ be done.
Nobody can argue with the possibility, but from experience we know that you are going down a road that will bite you later.
That said, the best way to achieve what you wish to do is by adding columns that explain the domain of a type on a per-row basis.
The fact that inside of SQLite an Integer can be stored in different ways is simply a code/data/space optimisation for SQLite, it is
transparent to the user and transparent to the SQL - it is in no way intended as a data-feature or extension of the SQL Language. If
you try to build on top of that (or maybe not on top of it, but in the same way) more arbitrary sub-types, yes of course it is
possible, but it may leave you in a World of hurt in terms of using the DB as a relational database system for other normal activities.
In contrast, if this is intended more as an application file-format than RDBMS, sure, make it better but, as others pointed out,
still try to do so in a way that does not require maintaining a software fork. This is good advice and in no way forbidding or
prescribing, just letting you know what will make life easier for you in the long run.
From the tone of your last post (if I am reading correctly) I understand that you have your mind set on finding a way that you have
thought about a lot lying in your bed late at night, you have rolled it around in your head and you just feel this should be
doable and will be so elegant a solution. We all go through that. You probably came here looking for confirmation of your ideas
rather than advice, but many of the people here have already done what you are trying now, this is why they know and this is why
this list is useful.
Y'know, use it, don't use it, etc. :)
On 2014/11/26 13:22, Darko Volaric wrote:
That's not true. A 64 bit floating point number and an 64 bit integer can
be represented by the exact same 64 bit pattern, and no-one would suggest
they're the same value. You can have those two differently typed although
identical bit values in the same SQLite column. The data identifying the
representation of those datums is integral to that value and doesn't belong
in a different column as you say. Other SQL based systems also allow
differently represented (typed) values to appear in the same column.
The domain of a column can logically incorporate these different kinds of
values by introducing the concept of subtype. For instance in SQLite there
are something like 6 different representations of integer of between 1 and
64 bits. Each one of those is a different type in the sense of having a
different representation due to the number of bits they use and being
limited to a different set of numbers. A 1 bit integer is a subtype of a 64
bit integer since the set of numbers allowed by the 1 bit integer is {0, 1}
are contained with the set of numbers allowed by 64 bit integers, ie
{0..2^64-1}. If the column has a domain of integer then all these values
are valid since they're all integers. There is no logical or formal reason
why this can't be extended further to allow arbitrary subtypes according to
the user's wishes.
You can have the same 64 bits of data represent 4 different values in
SQLite: integer, double, string and blob (I'm assuming SQLite can store a
blob in 8 bytes). They are not treated as equal by SQLite because they have
different types. There is no reason why we should be limited to those 4
types in SQLite. Many SQL based system allow users to define their own
types. What I'm proposing is just implementing the same thing in SQLite.
There is nothing in the relational model that disallows this. You're
assuming that because columns have a domain or type, then that domain must
have a fixed representation in the database implementation. The relational
model says nothing about how the data is represented or what kind of data
can be stored.
On Tue, Nov 25, 2014 at 5:20 PM, James K. Lowden jklow...@schemamania.org
wrote:
On Tue, 25 Nov 2014 04:41:51 -0800
Darko Volaric li...@darko.org wrote:
I have a need to implement per-value custom typing in SQLite. In my
case I may have the integer value 1234 appear in two rows in the same
column, but they are different in that they have completely different
meanings because they have different types although they have the
same representation in the database.
A column is a type. Two values of the same type *must* represent the
same thing. That's how SQL works.
When you say 1234 may represent two different things in the same
column, what you really mean is that 1234 doesn't stand for the whole
thing, that the column isn't the whole type (as you
Re: [sqlite] Corrupting pointers to the lookaside smallacator
We observe very similar problem.
#1 0x087ec9f7 in sqlite3VXPrintf ()
#2 0x087f816d in sqlite3MPrintf ()
#3 0x088781e5 in sqlite3VtabFinishParse ()
#4 0x0885190f in yy_reduce ()
#5 0x0884d4d8 in sqlite3Parser ()
#6 0x087fc0ce in sqlite3RunParser ()
#7 0x088aa396 in sqlite3Prepare ()
#8 0x087fae18 in sqlite3LockAndPrepare ()
#9 0x087f9a88 in sqlite3_exec ()
#10 0x086588a7 in sqlite::StorageBase::exec_query (this=0x2c2a47c0,
query=0x2c7fffc0 CREATE VIRTUAL TABLE temp.vtb_wayxzmbo USING
vtable_module_344, quiet=false) at SqliteStorageBase.cpp:286
It always crashes when CREATE VIRTUAL TABLE ... is being executed, always
with the same backtrace.
I spent many days reviewing and testing my code to eliminate possible cause but
so far I see nothing wrong with it.
Probability of this crash is so very low so that problem can be reproduced only
on hi loaded production servers.
(Where such virtual tables are created and dropped millions of times during a
day)
I am going to compile sqlite without optimizations and with debug symbols and
wait for a crash
to try and track the root of the problem from within sqlite.
Though I doubt very much this is sqlite problem at all and not an incorrect
vtable implementation on my side.
SQLite version 3.8.6 2014-08-15 11:46:33
We are compiling the 3.8.7.1 using clang arm64 for iOS. Following set:
#define SQLITE_ENABLE_COLUMN_METADATA 1
#define HAVE_INTTYPES_H 1
#define HAVE_STDINT_H 1
#define HAVE_USLEEP 1
#define SQLITE_DEBUG 1
#define SQLITE_MEMDEBUG 1
WAL mode.
In MallowRaw(), very rarely, seeing the lookaside buffer pBuf or pBuf-next
getting corrupted with ASCII from our CREATE TABLE statements. ('INTEGER' or
part of one of our field names). Thing is, we are not running the schema
create code on these runs (the DB already exists), so these strings must be
coming from sqlite_master, AFAIKT.
None of the SQLITE_DEBUG or SQLITE_MEMDEBUG asserts fire. When it happens, it
happens early in the application run.
Hard to set a hardware write breakpoint on such a mutable thing.
I fully believe the problem is of my own creation, but interested in any
thoughts or if anyone has seen anything like this.
Thanks, as always,
-- Ward
___
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] [sqlite-dev] Implementing per-value custom types
The person I replied to cross posted, not I, and I didn't realise this
before I replied to his cross post and the I couldn't change it then, so
maybe take that up with him.
I'm not confusing anything. You, and the other posters, are confusing the
representation of values and the concrete value type with the logical types
declared for column domains. I understand this might be a subtle
distinction to some.
But I don't intend to argue this point any further since I'm merely looking
for advice about how the database engine is implemented, not about how it's
used or how I'm using it.
I'm not looking for confirmation of ideas, on the contrary, people seem to
want to push their own ideas about a database should be used and how I'm
not using it correctly, when that is irrlevent to the issue I'm discussing.
Maybe more focus on the technical facts and less on divining what I think
at night in bed and try framing your arguments based on those technical
facts rather than ad-hominem attacks.
On Wed, Nov 26, 2014 at 4:53 AM, RSmith rsm...@rsweb.co.za wrote:
Hi Darko,
Firstly, kindly keep this to the sqlite-users forum and not on the dev
forum (the devs read this too, the difference being simply that this one
exists to help you, the other one is to discuss development stuff, not to
help anyone).
Secondly, you are confusing two things. You are arguing about what /CAN/
be done while James tried to explain what /Should/ be done. Nobody can
argue with the possibility, but from experience we know that you are going
down a road that will bite you later.
That said, the best way to achieve what you wish to do is by adding
columns that explain the domain of a type on a per-row basis. The fact that
inside of SQLite an Integer can be stored in different ways is simply a
code/data/space optimisation for SQLite, it is transparent to the user and
transparent to the SQL - it is in no way intended as a data-feature or
extension of the SQL Language. If you try to build on top of that (or maybe
not on top of it, but in the same way) more arbitrary sub-types, yes of
course it is possible, but it may leave you in a World of hurt in terms of
using the DB as a relational database system for other normal activities.
In contrast, if this is intended more as an application file-format than
RDBMS, sure, make it better but, as others pointed out, still try to do so
in a way that does not require maintaining a software fork. This is good
advice and in no way forbidding or prescribing, just letting you know what
will make life easier for you in the long run.
From the tone of your last post (if I am reading correctly) I understand
that you have your mind set on finding a way that you have thought about a
lot lying in your bed late at night, you have rolled it around in your head
and you just feel this should be doable and will be so elegant a
solution. We all go through that. You probably came here looking for
confirmation of your ideas rather than advice, but many of the people here
have already done what you are trying now, this is why they know and this
is why this list is useful.
Y'know, use it, don't use it, etc. :)
On 2014/11/26 13:22, Darko Volaric wrote:
That's not true. A 64 bit floating point number and an 64 bit integer can
be represented by the exact same 64 bit pattern, and no-one would suggest
they're the same value. You can have those two differently typed although
identical bit values in the same SQLite column. The data identifying the
representation of those datums is integral to that value and doesn't
belong
in a different column as you say. Other SQL based systems also allow
differently represented (typed) values to appear in the same column.
The domain of a column can logically incorporate these different kinds of
values by introducing the concept of subtype. For instance in SQLite there
are something like 6 different representations of integer of between 1 and
64 bits. Each one of those is a different type in the sense of having a
different representation due to the number of bits they use and being
limited to a different set of numbers. A 1 bit integer is a subtype of a
64
bit integer since the set of numbers allowed by the 1 bit integer is {0,
1}
are contained with the set of numbers allowed by 64 bit integers, ie
{0..2^64-1}. If the column has a domain of integer then all these values
are valid since they're all integers. There is no logical or formal reason
why this can't be extended further to allow arbitrary subtypes according
to
the user's wishes.
You can have the same 64 bits of data represent 4 different values in
SQLite: integer, double, string and blob (I'm assuming SQLite can store a
blob in 8 bytes). They are not treated as equal by SQLite because they
have
different types. There is no reason why we should be limited to those 4
types in SQLite. Many SQL based system allow users to define their own
types. What I'm proposing is just
Re: [sqlite] Infinite loop in sqlite3VdbeSorterWrite when sorting big data
Thanks for reporting this. I think it should be fixed here: http://www.sqlite.org/src/info/623827192532f08b Thanks for the fix! I confirm it fixes the issue I saw. Marcin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Implementing per-value custom types
On 2014/11/26 15:58, Darko Volaric wrote: I'm not looking for confirmation of ideas, on the contrary, people seem to want to push their own ideas about a database should be used and how I'm not using it correctly, when that is irrlevent to the issue I'm discussing. Maybe more focus on the technical facts and less on divining what I think at night in bed and try framing your arguments based on those technical facts rather than ad-hominem attacks. I am sincerely sorry if you construed my reply as anything remotely ad hominem - it surely wasn't, and it's not pushing ideas on you, it's giving advice or alternates because your ideas are short-sighted and hard to implement. We are however very nice on here since sharing knowledge is a passion, and that's why we say nice things like we understand how it feels to have ideas and then offer some advice... not because we spend our days divining about your life. You throw everyone's advice back in their faces and are arrogant about it - well, even to that we are still nice and willing to answer the questions - kindly accept it in that spirit. But I don't intend to argue this point any further since I'm merely looking for advice about how the database engine is implemented, not about how it's used or how I'm using it. Ok, sticking to the facts, the database engine is implemented in a way that makes your original suggested options pan out like this: 1 - very easy for the engine, very work intensive for you. 2 - still easy for the engine, although it will lose most RDBMS querying value, and still cumbersome for you (Maybe best to use Virtual tables to implement this), and 3 - impossible without a dedicated fork, and even then very difficult. I wouldn't personally pick any of those, but if those were the only options in life and I had to pick one, knowing SQLite, I'd probably lean more towards option 2 than the others. SQLite is loosely typed, but it is still typed, and the typing mechanism is not open to the API and every one of the hundreds of core functions in SQLite are specifically coded to dance with those few primary types. Adding/Altering it must always be the very last option on any list. Good luck, Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Network and concurrency
Hello, I am continuing to have sporadic problems with SQLite and a WinForms application that I'm developing. The program is a Winforms app using the System.Data.SQLite class. I am running this off a network. There are a small number of users which read and write to a database stored on a network. There are locking errors sometimes or sometimes when performing an operation the program will simply become locked up and eventually crash because the UI thread gets locked up. I tried to put some of these calls on separate threads, but for some reason the lockups still occur. Are there any other PRAGMA or connection string statements that can help with this concurrency issue? Thanks Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Network and concurrency
On 26 Nov 2014, at 4:20pm, Mike McWhinney elja...@sbcglobal.net wrote: Are there any other PRAGMA or connection string statements that can help with this concurrency issue? If you haven't set a busy timeout then SQlite won't retry when there is a network clash, it will immediately return an error code. If you have the ability to use PRAGMAs then I suggest you set your timeout http://www.sqlite.org/pragma.html#pragma_busy_timeout to 6 milliseconds for testing. If this makes your problem go away that should identify your problem. Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Implementing per-value custom types
You wrote: From the tone of your last post (if I am reading correctly) I understand that you have your mind set on finding a way that you have thought about a lot lying in your bed late at night, you have rolled it around in your head and you just feel this should be doable and will be so elegant a solution. That references only me, in a condescending way, without referencing the actual issue. That's ad-hominem. You've made it clear by your posts you don't understand the SQLite issue I'm talking about so I'm not discussing that further, as I have already said. If you want to address anything to do with me, then email me directly at take it off list, so as to stop wasting other people's time. On Wed, Nov 26, 2014 at 8:20 AM, RSmith rsm...@rsweb.co.za wrote: On 2014/11/26 15:58, Darko Volaric wrote: I'm not looking for confirmation of ideas, on the contrary, people seem to want to push their own ideas about a database should be used and how I'm not using it correctly, when that is irrlevent to the issue I'm discussing. Maybe more focus on the technical facts and less on divining what I think at night in bed and try framing your arguments based on those technical facts rather than ad-hominem attacks. I am sincerely sorry if you construed my reply as anything remotely ad hominem - it surely wasn't, and it's not pushing ideas on you, it's giving advice or alternates because your ideas are short-sighted and hard to implement. We are however very nice on here since sharing knowledge is a passion, and that's why we say nice things like we understand how it feels to have ideas and then offer some advice... not because we spend our days divining about your life. You throw everyone's advice back in their faces and are arrogant about it - well, even to that we are still nice and willing to answer the questions - kindly accept it in that spirit. But I don't intend to argue this point any further since I'm merely looking for advice about how the database engine is implemented, not about how it's used or how I'm using it. Ok, sticking to the facts, the database engine is implemented in a way that makes your original suggested options pan out like this: 1 - very easy for the engine, very work intensive for you. 2 - still easy for the engine, although it will lose most RDBMS querying value, and still cumbersome for you (Maybe best to use Virtual tables to implement this), and 3 - impossible without a dedicated fork, and even then very difficult. I wouldn't personally pick any of those, but if those were the only options in life and I had to pick one, knowing SQLite, I'd probably lean more towards option 2 than the others. SQLite is loosely typed, but it is still typed, and the typing mechanism is not open to the API and every one of the hundreds of core functions in SQLite are specifically coded to dance with those few primary types. Adding/Altering it must always be the very last option on any list. Good luck, Ryan ___ 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] Network and concurrency
I still get the crashes even after adding the pragma. My connection string looks like: public static string OMconnectionString = URI=file:omm.db; PRAGMA busy_timeout = 15000 Is there anything else I can try? Mike On Wednesday, November 26, 2014 10:34 AM, Simon Slavin slav...@bigfraud.org wrote: On 26 Nov 2014, at 4:20pm, Mike McWhinney elja...@sbcglobal.net wrote: Are there any other PRAGMA or connection string statements that can help with this concurrency issue? If you haven't set a busy timeout then SQlite won't retry when there is a network clash, it will immediately return an error code. If you have the ability to use PRAGMAs then I suggest you set your timeout http://www.sqlite.org/pragma.html#pragma_busy_timeout to 6 milliseconds for testing. If this makes your problem go away that should identify your problem. Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Implementing per-value custom types
a type column to go with variant data would probably be best... how many columns do you have that are actually self-described typedata required? could just serialize it to a blob; include type, and the value... kinda hard to select for a value that way... at least if it's a parallel type the value can be a simple representation. variant types can either be handled with a container that contains a type indicator (VB-like) or just kept as a string, and when required to be a value, parsed to see if it can apply... why not just break out separate tables per type that link back to the data row id? For options, started with a value table that was (value_id,int,string,blob), later broke it out to 3 tables (option_id,int) (option_id,string) (option_id,blob) ... I used to create the value_id and store that back in the option map; realized I could just use the map id to get the value instead... THe other type of variable data I ran into was a bank ledger that had a 'operation' field and a value field, and a couple related account IDs.. where the procedure to do with the value was determined variably rather than the data type... If I were to do it again, I'd break out transaction types to separate tables... yes, it complicates queireis requiring joins, but for later general tool use it's easier to cope with. How many applications are really storing variable types of data? Will new applications also write new kinds of data that were previously ununderstood? Will old things still work? On Wed, Nov 26, 2014 at 9:25 AM, Darko Volaric li...@darko.org wrote: You wrote: From the tone of your last post (if I am reading correctly) I understand that you have your mind set on finding a way that you have thought about a lot lying in your bed late at night, you have rolled it around in your head and you just feel this should be doable and will be so elegant a solution. That references only me, in a condescending way, without referencing the actual issue. That's ad-hominem. You've made it clear by your posts you don't understand the SQLite issue I'm talking about so I'm not discussing that further, as I have already said. If you want to address anything to do with me, then email me directly at take it off list, so as to stop wasting other people's time. On Wed, Nov 26, 2014 at 8:20 AM, RSmith rsm...@rsweb.co.za wrote: On 2014/11/26 15:58, Darko Volaric wrote: I'm not looking for confirmation of ideas, on the contrary, people seem to want to push their own ideas about a database should be used and how I'm not using it correctly, when that is irrlevent to the issue I'm discussing. Maybe more focus on the technical facts and less on divining what I think at night in bed and try framing your arguments based on those technical facts rather than ad-hominem attacks. I am sincerely sorry if you construed my reply as anything remotely ad hominem - it surely wasn't, and it's not pushing ideas on you, it's giving advice or alternates because your ideas are short-sighted and hard to implement. We are however very nice on here since sharing knowledge is a passion, and that's why we say nice things like we understand how it feels to have ideas and then offer some advice... not because we spend our days divining about your life. You throw everyone's advice back in their faces and are arrogant about it - well, even to that we are still nice and willing to answer the questions - kindly accept it in that spirit. But I don't intend to argue this point any further since I'm merely looking for advice about how the database engine is implemented, not about how it's used or how I'm using it. Ok, sticking to the facts, the database engine is implemented in a way that makes your original suggested options pan out like this: 1 - very easy for the engine, very work intensive for you. 2 - still easy for the engine, although it will lose most RDBMS querying value, and still cumbersome for you (Maybe best to use Virtual tables to implement this), and 3 - impossible without a dedicated fork, and even then very difficult. I wouldn't personally pick any of those, but if those were the only options in life and I had to pick one, knowing SQLite, I'd probably lean more towards option 2 than the others. SQLite is loosely typed, but it is still typed, and the typing mechanism is not open to the API and every one of the hundreds of core functions in SQLite are specifically coded to dance with those few primary types. Adding/Altering it must always be the very last option on any list. Good luck, Ryan ___ 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] Network and concurrency
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 Specifically the last section. On Wed, Nov 26, 2014 at 1:30 PM, Mike McWhinney elja...@sbcglobal.net wrote: I still get the crashes even after adding the pragma. My connection string looks like: public static string OMconnectionString = URI=file:omm.db; PRAGMA busy_timeout = 15000 Is there anything else I can try? Mike On Wednesday, November 26, 2014 10:34 AM, Simon Slavin slav...@bigfraud.org wrote: On 26 Nov 2014, at 4:20pm, Mike McWhinney elja...@sbcglobal.net wrote: Are there any other PRAGMA or connection string statements that can help with this concurrency issue? If you haven't set a busy timeout then SQlite won't retry when there is a network clash, it will immediately return an error code. If you have the ability to use PRAGMAs then I suggest you set your timeout http://www.sqlite.org/pragma.html#pragma_busy_timeout to 6 milliseconds for testing. If this makes your problem go away that should identify your problem. 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
Re: [sqlite] Implementing per-value custom types
Darko,
I have rather a long answer for you. I'm not confused about the
difference between logical and physical types, but I am confused about
what you meant and what you're hoping to accomplish.
On Wed, 26 Nov 2014 03:22:03 -0800
Darko Volaric li...@darko.org wrote:
A 64 bit floating point number and an 64 bit integer can be
represented by the exact same 64 bit pattern, and no-one would
suggest they're the same value.
Because they're different types.
You can have those two differently typed although identical bit
values in the same SQLite column.
Because SQLite doesn't enforce type-checking.
The data identifying the representation of those datums is integral
to that value and doesn't belong in a different column as you say.
The data identifying the representation is type information.
You're assuming that because columns have a domain or type, then that
domain must have a fixed representation in the database
implementation.
I think it's a reasonable design choice for the machines we have. In
any event, the DBMS's choice of representation is its own.
Looking at your other replies, I'm not sure I understand what you're
hoping to accomplish. I answered your assertion that you'd like to
have multi-type columns:
I have a need to implement per-value custom typing in SQLite. In my
case I may have the integer value 1234 appear in two rows in the
same column, but they are different in that they have completely
different meanings because they have different types although they
have the same representation in the database.
Where you lose me is the idea that two rows in one column might have
identical bit patterns but mean different things. Computers and
databases generally represent values as bits. Surely two identical
values of one type are equal. You have to store the distinction
somewhere, right?
Elsewhere you suggested you want to *extend* the type system, to define
more complex types (for which identical bit patterns are equal). You
could define them in terms of contraints,
A 1 bit integer is a subtype of a 64 bit integer since the set of
numbers allowed by the 1 bit integer is {0, 1} are contained with the
set of numbers allowed by 64 bit integers, ie {0..2^64-1}.
This as you know can already be done using CHECK constraints.
There is no logical or formal reason why this can't be extended
further to allow arbitrary subtypes according to the user's wishes.
I think I would argue that's true, provided:
1. the constraints can be expressed to the evaluation system
2. further-constrained types don't necessary represent a subtype
After all, a 1-bit integer might as well be a Boolean or for that
matter might represent red and green. You can't add red and green in a
1-bit regime, nor divide, nor increment. It's a subtype only in some
physical sense, the very sense you're saying is irrelevant.
I also think such constraints can be expressed now using DRI. Is there
something you'd like to enforce that you cannot?
Aggregate types are a different matter. For example, one might want to
have a PDF type. PDFs can contain certain metadata, such as author or
date. Comparison could be defined in terms of those metadata;
constraints could require them. If you had a way to signify the type
and comparitor to SQLite, you could extend its type system and
distinguish PDFs from ordinary blobs.
We can go one step further: a column may hold tables. Nothing in RM
prevents that either; it's just not supported by SQL.
I don't think you're going to find much appetite for a user-extensible
type system in SQLite. It's harder than it seems on both sides,
implementation and use. And without very deep support -- in the
constraint system, in the SQL syntax -- it would probably do more harm
than good.
I'm not sure where that leaves us. It's one thing to distinguish
between logical and physical types. It's another to extend the type
system. It's a third to argue that because a logical type could be
represented in more than one way physically, that it should be, in one
system, in one column. And it's yet a fourth to say that a column
should be able to represent more than one type, be it physical or
logical. I'm not sure which of those you mean.
--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] creating trigger to handle multiple types of insert
There are two different types of insert that need to happen into a table,
one is where the rowid (EventNodeId) is part of the actual insert, the
other it is excluded so that AUTOINCREMENT will fill it in. There is an
insert triggers to set the audit fields on the table during the insert.
The question is how to make the trigger work for both cases. Here is the
table and the trigger:
CREATE TABLE EventNode(
EventNodeId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
ObjectId VARCHAR(50) NOT NULL UNIQUE,
Name VARCHAR(100) NOT NULL,
SortName VARCHAR(100),
IsActive INTEGER DEFAULT 1 NOT NULL,
NodeType INTEGER NOT NULL,
Deleted CHAR(1) DEFAULT 'N' NOT NULL,
lft INTEGER NOT NULL,
rgt INTEGER NOT NULL,
insertedby VARCHAR(50) NOT NULL,
instertedon TIMESTAMP NOT NULL,
updatedby VARCHAR(50) NOT NULL,
updatedon TIMESTAMP NOT NULL );
CREATE TRIGGER ti_EventNode_standard BEFORE INSERT ON EventNode
BEGIN
INSERT INTO EventNode ( EventNodeId, ObjectId, Name, IsActive,
NodeType, lft, rgt, insertedby, instertedon, updatedby, updatedon)
VALUES (new.EventNodeId, new.ObjectId, new.Name, new.IsActive,
new.NodeType, new.lft, new.rgt,
new.insertedby, julianday('now'),
new.insertedby, julianday('now'));
SELECT RAISE(IGNORE);
END;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating trigger to handle multiple types of insert
On 11/26/2014 8:58 PM, Sam Carleton wrote: The question is how to make the trigger work for both cases. Experimentally, when EventNodeId is omitted from INSERT statement, new.EventNodeId == -1 in the trigger. So I think something like this should work: INSERT INTO EventNode ( EventNodeId, ...) VALUES( (case when new.EventNodeId 0 then null else new.EventNodeId end), ...); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
