See this answer: https://stackoverflow.com/questions/67127/database-functionality-with-wpf-app-sqlite-sql-ce-other 
- SQL Compact 4 has full support for Entity Framework, Linq to SQL
- Includes all neccesary file for private deployment
- has good tools support in Visual Studio 2010 and 2012
- has a smooth migration path to SQL Server



This is a question that keeps recurring in all of my programming, python and otherwise. I really like to keep my code under 80 chars if at all possible/not horribly ugly. In a language like Perl, this isn&#39;t too hard since white space doesn&#39;t matter. In Python, where it does, I wind up knocking my head against the wall more often than I&#39;d like to be trying to think of a &quot;nice&quot; way of splitting up my long lines. So, Code gurus, how do you do it? Any general strategies you can inform me of?

A particular problem I&#39;m dealing with right now is:

    self.SomeLongLongName = SomeLongLongName.SomeLongLongName(some_obj, self.user1, self.user2)

When I naturally try to cut this off in Python, the only half decent way available to me seems to be:

    self.SomeLongLongName = SomeLongLongName.SomeLongLongName(some_obj,
                                                              self.user1
                                                              self.user2)

It doesn&#39;t look that bad, I guess, but it takes up three lines, which is just completely unnecessary. There must be a better way, no?

Note: I know there are those of you who don&#39;t like 80 chars a line and have created your own limits. I understand the motivation behind this and respect it, but 80 chars is my preferred limit. Please don&#39;t take up space trying to convince me to go over to 120 or some such here.

How Do I Keep Python Code Under 80 Chars Without Making It Ugly?

The Mystery
====
In exploring precisely which characters were permitted in Java identifiers, I have stumbled upon something so extremely curious that it seems nearly certain to be a bug. 

 I’d expected to find that Java identifiers conformed to the requirement that they start with characters that have the Unicode property `ID_Start` and are followed by those with the property `ID_Continue`, with an exception granted for leading underscores and for dollar signs. That did not prove to be the case, and what I found is at extreme variance with that or any other idea of a normal identifier that I have heard of.
Short Demo
=========
Consider the following demonstration proving that an ASCII ESC character (octal 033) is permitted in Java identifiers:

    $ perl -le &#39;print qq(public class escape { public static void main(String argv[]) { String var_\033 = &quot;i am escape: \033&quot;; System.out.println(var_\033); }})&#39; &gt; escape.java
    $ javac escape.java
    $ java escape | cat -v
    i am escape: ^[

It’s even worse than that, though.  Almost infinitely worse, in fact.  Even NULLs are permitted! And thousands of other code points that are not even identifier characters. I have tested this on Solaris, Linux, and a Mac running Darwin, and all give the same results.

Long Demo
=====
Here is a test program that will show all these unexpected code points that Java quite outrageuosly allows as part of a legal identifier name.

    #!/usr/bin/env perl
    # 
    # test-java-idchars - find which bogus code points Java allows in its identifiers
    # 
    #   usage: test-java-idchars [low high]
    #   e.g.:  test-java-idchars 0 255
    #
    # Without arguments, tests Unicode code points
    # from 0 .. 0x1000.  You may go further with a
    # higher explicit argument.
    #
    # Produces a report at the end.
    #
    # You can ^C it prematurely to end the program then
    # and get a report of its progress up to that point.
    #
    # Tom Christiansen
    # tchrist@perl.com
    # Sat Jan 29 10:41:09 MST 2011
    
    use strict;
    use warnings;
    
    use encoding &quot;Latin1&quot;;
    use open IO =&gt; &quot;:utf8&quot;;
    
    use charnames ();
    
    $| = 1;
    
    my @legal;
    
    my ($start, $stop) = (0, 0x1000);
    
    if (@ARGV != 0) {
        if (@ARGV == 1) {
            for (($stop) = @ARGV) { 
                $_ = oct if /^0/;   # support 0OCTAL, 0xHEX, 0bBINARY
            }
        }
        elsif (@ARGV == 2) {
            for (($start, $stop) = @ARGV) { 
                $_ = oct if /^0/;
            }
        } 
        else {
            die &quot;usage: $0 [ [start] stop ]\n&quot;;
        } 
    } 
    
    for my $cp ( $start .. $stop ) {
        my $char = chr($cp);
    
        next if $char =~ /[\s\w]/;
    
        my $type = &quot;?&quot;;
        for ($char) {
            $type = &quot;Letter&quot;      if /\pL/;
            $type = &quot;Mark&quot;        if /\pM/;
            $type = &quot;Number&quot;      if /\pN/;
            $type = &quot;Punctuation&quot; if /\pP/;
            $type = &quot;Symbol&quot;      if /\pS/;
            $type = &quot;Separator&quot;   if /\pZ/;
            $type = &quot;Control&quot;     if /\pC/;
        } 
        my $name = $cp ? (charnames::viacode($cp) || &quot;&lt;missing&gt;&quot;) : &quot;NULL&quot;;
        next if $name eq &quot;&lt;missing&gt;&quot; &amp;&amp; $cp &gt; 0xFF;
        my $msg = sprintf(&quot;U+%04X %s&quot;, $cp, $name);
        print &quot;testing \\p{$type} $msg...&quot;;
        open(TESTPROGRAM, &quot;&gt;:utf8&quot;, &quot;testchar.java&quot;) || die $!;
    
    print TESTPROGRAM &lt;&lt;&quot;End_of_Java_Program&quot;;
    
    public class testchar { 
        public static void main(String argv[]) { 
            String var_$char = &quot;variable name ends in $msg&quot;;
            System.out.println(var_$char); 
        }
    }
    
    End_of_Java_Program
    
        close(TESTPROGRAM) || die $!;
    
        system q{
            ( javac -encoding UTF-8 testchar.java \
                &amp;&amp; \
              java -Dfile.encoding=UTF-8 testchar | grep variable \
            ) &gt;/dev/null 2&gt;&amp;1
        };
    
        push @legal, sprintf(&quot;U+%04X&quot;, $cp) if $? == 0;
    
        if ($? &amp;&amp; $? &lt; 128) {
            print &quot;&lt;interrupted&gt;\n&quot;;
            exit;  # from a ^C
        } 
    
        printf &quot;is %s in Java identifiers.\n&quot;,  
            ($? == 0) ? uc &quot;legal&quot; : &quot;forbidden&quot;;
    
    } 
    
    END { 
        print &quot;Legal but evil code points: @legal\n&quot;;
    }

Here is a sample of running that program on just the first 33 code points that are neither whitespace nor identifier characters:

    $ perl test-java-idchars 0 0x20
    testing \p{Control} U+0000 NULL...is LEGAL in Java identifiers.
    testing \p{Control} U+0001 START OF HEADING...is LEGAL in Java identifiers.
    testing \p{Control} U+0002 START OF TEXT...is LEGAL in Java identifiers.
    testing \p{Control} U+0003 END OF TEXT...is LEGAL in Java identifiers.
    testing \p{Control} U+0004 END OF TRANSMISSION...is LEGAL in Java identifiers.
    testing \p{Control} U+0005 ENQUIRY...is LEGAL in Java identifiers.
    testing \p{Control} U+0006 ACKNOWLEDGE...is LEGAL in Java identifiers.
    testing \p{Control} U+0007 BELL...is LEGAL in Java identifiers.
    testing \p{Control} U+0008 BACKSPACE...is LEGAL in Java identifiers.
    testing \p{Control} U+000B LINE TABULATION...is forbidden in Java identifiers.
    testing \p{Control} U+000E SHIFT OUT...is LEGAL in Java identifiers.
    testing \p{Control} U+000F SHIFT IN...is LEGAL in Java identifiers.
    testing \p{Control} U+0010 DATA LINK ESCAPE...is LEGAL in Java identifiers.
    testing \p{Control} U+0011 DEVICE CONTROL ONE...is LEGAL in Java identifiers.
    testing \p{Control} U+0012 DEVICE CONTROL TWO...is LEGAL in Java identifiers.
    testing \p{Control} U+0013 DEVICE CONTROL THREE...is LEGAL in Java identifiers.
    testing \p{Control} U+0014 DEVICE CONTROL FOUR...is LEGAL in Java identifiers.
    testing \p{Control} U+0015 NEGATIVE ACKNOWLEDGE...is LEGAL in Java identifiers.
    testing \p{Control} U+0016 SYNCHRONOUS IDLE...is LEGAL in Java identifiers.
    testing \p{Control} U+0017 END OF TRANSMISSION BLOCK...is LEGAL in Java identifiers.
    testing \p{Control} U+0018 CANCEL...is LEGAL in Java identifiers.
    testing \p{Control} U+0019 END OF MEDIUM...is LEGAL in Java identifiers.
    testing \p{Control} U+001A SUBSTITUTE...is LEGAL in Java identifiers.
    testing \p{Control} U+001B ESCAPE...is LEGAL in Java identifiers.
    testing \p{Control} U+001C INFORMATION SEPARATOR FOUR...is forbidden in Java identifiers.
    testing \p{Control} U+001D INFORMATION SEPARATOR THREE...is forbidden in Java identifiers.
    testing \p{Control} U+001E INFORMATION SEPARATOR TWO...is forbidden in Java identifiers.
    testing \p{Control} U+001F INFORMATION SEPARATOR ONE...is forbidden in Java identifiers.
    Legal but evil code points: U+0000 U+0001 U+0002 U+0003 U+0004 U+0005 U+0006 U+0007 U+0008 U+000E U+000F U+0010 U+0011 U+0012 U+0013 U+0014 U+0015 U+0016 U+0017 U+0018 U+0019 U+001A U+001B

And here is another demo:

    $ perl test-java-idchars 0x600 0x700 | grep -i legal
    testing \p{Control} U+0600 ARABIC NUMBER SIGN...is LEGAL in Java identifiers.
    testing \p{Control} U+0601 ARABIC SIGN SANAH...is LEGAL in Java identifiers.
    testing \p{Control} U+0602 ARABIC FOOTNOTE MARKER...is LEGAL in Java identifiers.
    testing \p{Control} U+0603 ARABIC SIGN SAFHA...is LEGAL in Java identifiers.
    testing \p{Control} U+06DD ARABIC END OF AYAH...is LEGAL in Java identifiers.
    Legal but evil code points: U+0600 U+0601 U+0602 U+0603 U+06DD

The Question
=======
Can anyone please explain this seemingly insane behavior?  There are many, many, many other inexplicably permitted code points all over the place, starting right off with U+0000, which is perhaps the strangest of all.  If you run it on the first 0x1000 code points, you do see certain patterns appear, such as permitting any and all code points with the property `Current_Symbol`.  But too much else is wholly inexplicable, at least by me.



Why does Java allow control characters in its identifiers?

I&#39;m interested in choosing an embedded DB engine for my WPF app. I would like information on how SQLite and latest SQL CE 4.0 compare. And if I should choose one over the other. Points of interest would be ORM support with EF4&#39;s CTP &amp; NHibernate 3.

Thanks in advance!

SQL Server Compact 4.0 vs. SQLite

<p>I'm interested in choosing an embedded DB engine for my WPF app. I would like information on how SQLite and latest SQL CE 4.0 compare. And if I should choose one over the other. Points of interest would be ORM support with EF4's CTP &#x26; NHibernate 3.</p>
<p>Thanks in advance!</p>


I have a table layout that I want to populate with the result from a database query. I use a select all and the query returns four rows of data. 

I use this code to populate the TextViews inside the table rows.

```java
Cursor c = null;
c = dh.getAlternative2();
startManagingCursor(c);
// the desired columns to be bound
String[] columns = new String[] {DataHelper.KEY_ALT};
// the XML defined views which the data will be bound to
int[] to = new int[] { R.id.name_entry};

SimpleCursorAdapter mAdapter = new SimpleCursorAdapter(this, 
		 R.layout.list_example_entry, c, columns, to);
this.setListAdapter(mAdapter);
```

I want to be able to separate the four different values of KEY_ALT, and choose where they go. I want them to populate four different TextViews instead of one in my example above.

How can I iterate through the resulting cursor?

Iterate through rows from Sqlite-query

I&#39;m constructing a query using SQLAlchemy and SQLite3 in which I&#39;d like to select rows in which a String column contains a particular substring. What is the best way to accomplish this?  

SQLAlchemy query where a column contains a substring

### Background

I have an Android project that has a database with two tables: `tbl_question` and `tbl_alternative`.

To populate the views with questions and alternatives I am using cursors. There are no problems in getting the data I need until I try to join the two tables. 


&lt;pre&gt;
    Tbl_question  
    -------------
    _id  
    question  
    categoryid  
&lt;/pre&gt;

&lt;pre&gt;
    Tbl_alternative
    ---------------
    _id 
    questionid 
    categoryid 
    alternative
&lt;/pre&gt; 

I want something like the following:

    SELECT tbl_question.question, tbl_alternative.alternative where 
    categoryid=tbl_alternative.categoryid AND tbl_question._id = 
    tbl_alternative.questionid.` 

This is my attempt: 

    public Cursor getAlternative(long categoryid) {
                String[] columns = new String[] { KEY_Q_ID, KEY_IMAGE, KEY_QUESTION, KEY_ALT, KEY_QID};
                 String whereClause = KEY_CATEGORYID + &quot;=&quot; + categoryid +&quot; AND &quot;+ KEY_Q_ID +&quot;=&quot;+ KEY_QID;
                 Cursor cursor = mDb.query(true, DBTABLE_QUESTION + &quot; INNER JOIN &quot;+ DBTABLE_ALTERNATIVE, columns, whereClause, null, null, null, null, null);
                 if (cursor != null) {
                      cursor.moveToFirst();
                 }
                 return cursor;

I find this way to form queries harder than regular SQL, but have gotten the advice to use this way since it is less error prone. 

### Question

How do I join two SQLite tables in my application?


How do I join two SQLite tables in my Android application?

Why this query doesn&#39;t work? :( I tried to replace nested IF statement &quot;...SET lkey = IF(lkey &gt;= 11, lkey - 5, IF(lkey &gt; 5, lkey + 2,lkey))&quot;


    UPDATE pages
    SET lkey = CASE lkey WHEN lkey &gt;= 11 THEN
    		lkey - 5
    	ELSE
    		CASE lkey WHEN lkey &gt; 5 THEN
    			lkey + 2
    		ELSE
    			lkey
    		END
    	END,
    	rkey = CASE lkey WHEN lkey &gt;= 11 THEN
    		rkey - 5
    	ELSE
    		CASE rkey WHEN rkey &lt; 11 THEN
    			rkey + 2
    		ELSE
    			rkey
    		END
    	END
    WHERE rkey &gt; 5 AND
    	lkey &lt; 12;



CASE statement in SQLite query

I&#39;d like to use a regular expression in sqlite, but I don&#39;t know how.

My table has got a column with strings like this: &quot;3,12,13,14,19,28,32&quot;
Now if I type &quot;where x LIKE &#39;3&#39;&quot; I also get the rows which contain values like 13 or 32,
but I&#39;d like to get only the rows which have exactly the value 3 in that string.

Does anyone know how to solve this?


How do I use regex in a SQLite query?

I&#39;m working with the Code First feature of Entity Framework and I&#39;m trying to figure out how I can specify the column data types that should be created when the database is auto-generated.

I have a simple model:

    public class Article
    {
        public int ArticleID { get; set; }

        public string Title { get; set; }
        public string Author { get; set; }
        public string Summary { get; set; }
        public string SummaryHtml { get; set; }
        public string Body { get; set; }
        public string BodyHtml { get; set; }
        public string Slug { get; set; }

        public DateTime Published { get; set; }
        public DateTime Updated { get; set; }

        public virtual ICollection&lt;Comment&gt; Comments { get; set; }
    }

When I run my application, a SQL CE 4.0 database is automatically created with the following schema:

![DB Schema][1]

So far, so good! However, the data I will be inserting into the `Body` and `BodyHtml` properties is routinely larger than the maximum allowed length for the `NVarChar` column type, so I want EF to generate `Text` columns for these properties.

However, I cannot seem to find a way to do this! After quite a bit of Googling and reading, I tried to specify the column type using `DataAnnotations`, from information found in [this answer][2]:

    using System.ComponentModel.DataAnnotations;

    ...

    [Column(TypeName = &quot;Text&quot;)]
    public string Body { get; set; }

This throws the following exception (when the database is deleted and the app is re-run):

    Schema specified is not valid. Errors: (12,6) : error 0040: The Type text is not qualified with a namespace or alias. Only PrimitiveTypes can be used without qualification.

But I have no idea what namespace or alias I should be specifying, and I couldn&#39;t find anything that would tell me.

I also tried changing the annotation as per [this reference][3]:

    using System.Data.Linq.Mapping;

    ...

    [Column(DbType = &quot;Text&quot;)]
    public string Body { get; set; }

In this case a database _is_ created, but the `Body` column is still an `NVarChar(4000)`, so it seems that annotation is ignored.

Can anyone help? This seems like it should be a fairly common requirement, yet my searching has been fruitless!


  [1]: http://i.stack.imgur.com/A4OI9.gif
  [2]: https://stackoverflow.com/questions/4829776/generate-money-type-fields-using-code-first-ef-ctp5/4833477#4833477
  [3]: http://msdn.microsoft.com/en-us/library/system.data.linq.mapping.columnattribute.dbtype.aspx

How do I specify that a property should generate a TEXT column rather than an nvarchar(4000)

In SQL Server Compact Edition in Visual Studio 2010 (maybe SQL Server and SQL in general, I don&#39;t know), this command works:

    DELETE FROM foods WHERE (name IN (&#39;chickens&#39;, &#39;rabbits&#39;))

but this command produces an error of: `Error near identifier f. Expecting OUTPUT.`

    DELETE FROM foods f WHERE (f.name IN (&#39;chickens&#39;, &#39;rabbits&#39;))



Why can&#39;t I use an alias in a DELETE statement?

In Visual Studio 2013 there is no longer an option to create `.sdf` SQL Server Compact databases in WebPages (WebMatrix) websites. I am no longer able to view `.sdf` database files in Visual Studio either. I downloaded the latest version of WebMatrix to open my `.sdf` database but WebMatrix crashes when I open my website. When I create a new empty site and add existing files, it crashes.

I searched for days for a way to view and edit my `.sdf` database and nothing is working. I can no longer get Navicat SQL Server app to open a database. I cannot get LinqPad to open an `.sdf` database file, I cannot get Compact Viewer to open or edit `.sdf` databases. I **can** _open_ `.sdf` databases with `SqlCEToolbox40.exe` but it only shows top 200 rows **and** it won&#39;t let me edit the tables.

Now I&#39;m completely stumped. How do I view **and** edit a `.sdf` database file?

Is Microsoft dropping support for SDF database files in Visual Studio?

when I tried to create a new SQL Server Compact file from Visual Studio -&gt; Add New Item, I don&#39;t see the &#39;Local Database&#39; option any more which was actually the way to add SQL Server Compact Database. Now, if it is really discontinued from Microsoft, then, what is the recommended Embedded Database technology from Microsoft for Desktop Applications ? I tried SQLite but deployment is headache for SQLite because, the user may not have correct version of Visual C++ distributable installed in his/her machine.  

Is SQL Server Compact discontinued from Visual Studio 2013?

I&#39;ve inherited C# code that has an awful lot of DateTimes where the Kind property is DateTimeKind.Unspecified.  These are fed into Datetime.ToUniversalTime() which gives back a UTC datetime (it adds 7 hours in my case). This is how ToUniversalTime() works; see MSDN.
 The problem is that these DateTimes are in fact already in UTC time.  They are pulled out of a SQL Server Compact 4.0 database.  They were stored there in UTC.  My main question is:

1.  How do I modify the Kind property of a DateTime so that it&#39;s UTC and not Unspecified?  I don&#39;t want to change the time or date.  So for example, a date of April 1st 2013, 9:05 am with its Kind property of &quot;Unspecified&quot; should become a datetime of April 1st 2013, 9:05 UTC.  

If I could be indulged with a follow up question(s), it would be:

2. Are values necessarily returned from Sql Server Compact as &quot;Unspecified&quot;?  Inside Sql Server Compact, they are being stored as type (datetime, not null).   Looking at the code, it appears they are just put in the database, but there are no provisions to mark them as UTC or anything.  Is there a better way to handle things?  Is there a slick way to ensure the DateTimes come out of SQL Compact as UTC?

Please let me know if I can provide more details.  I&#39;m new to this code base and still getting my brain around it, so I&#39;m having trouble describing the problem perfectly.

Dave

Content Type	Original Author	Original Content on Stackoverflow
Question	AVP06	View Question on Stackoverflow
Solution 1 - Sqlite	ErikEJ	View Answer on Stackoverflow

SQL Server Compact 4.0 vs. SQLite

Sqlite Problem Overview

Sqlite Solutions

Solution 1 - Sqlite

Why does Java allow control characters in its identifiers?

How Do I Keep Python Code Under 80 Chars Without Making It Ugly?

Attributions